Ok so I was running into some errors restoring a database and I ended up writing this little script that did the job.

Thought I would share.

Here’s how you look into a backup set to find out what logical files make up the backup set.

When you get the logical file names you can then write a restore script to restore the database to any name you would like as well as renaming the datafiles to match your new database name.  Here are the steps:

From Management Studio, run this command from the master database: restore filelistonly from

disk=’path\dbname.bak’ where path is the location of the backup set: 
restore filelistonly from disk='\\YourServer\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Email.bak' 

In this case it returned the following information:

LogicalName   PhysicalName                  Type       FileGroupName        Size                MaxSize              FileId

Email1        E:\Data\Email_Backup.mdf   D         PRIMARY              10770972672           35184372080640       1

Email1_log   E:\Log\Email_Backup.ldf      L         NULL                 4312465408          2199023255552         2

Then we build the restore database statement and change the PhysicalName and location of the file to a new name we prefer.  In this case it looked like this:

restore database Email from disk='\\YourServer\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Email.bak' 
with move 'Email1' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Email_data.mdf', 
move 'Email1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Email_log.ldf' 
go

Bata Bing Bata Boom rocking the new database..

This is super late and not fully documented as i would like but i have been getting several requests from people for this so i wanted to get this code out. Sorry for the delay a update to this will be coming soon.

Thanks for your patience. Remember this isn't a release version.

Download The Code