Presents your 
SQL SERVER E-NEWSLETTER for July 15, 2003

<------------------------------------------->

RESTORE A DATABASE WITH A NEW NAME

An occasion might arise when you'll need to restore a database under a
new name. Possible reasons for this include a simple name change or a
business requirement due to an acquisition.

When you restore a database under a different name, you have to set a
new location for each file in the database. If you don't already know which
files are in the database backup, you can run the RESTORE FILELISTONLY
statement to determine the number and names of files in the database
backup. Running RESTORE FILELISTONLY will give you information about your
backup, such as the logical names of the files, the physical names, whether
the file is a data file or log file, and so on.

Next, execute the RESTORE DATABASE statement, including the new name for
the database, the backup device from where it will be stored, 
NORECOVERY
if you have transaction logs (RECOVERY if you don't have transaction
logs), and the MOVE clause for each file to restore to a new location 
if the filenames already exist.

In the example below, we'll create a new database called MyNwind2_Test,
which will be a copy of MyNwind2. MyNwind2 contains two files:
MyNwind2_data and MyNwind2_log. Since MyNwind2 already exists, 
MyNwind2_data and MyNwind2_log will have to be moved to another location
during the restore.

USE master
GO

Now determine the number and names of the files in the backup.
(MyNwind_2 is the name of the backup device.)

RESTORE FILELISTONLY
   FROM MyNwind2

Finally, restore the files from MyNwind2 to MyNwind2_Test.

RESTORE DATABASE MyNwind2_Test
   FROM MyNwind2
   WITH RECOVERY,
   MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
   MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'
GO

J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about
technology for more than two decades.

----------------------------------------