July 12, 2003 at 11:12 pm
I have a backup of database( named Payroll), this database is running on my sql server, How can i restore this database with different name other than (Payroll) on this same server. is it possible ?
July 13, 2003 at 1:28 am
From BOL.
"How to restore a database with a new name (Transact-SQL)
To restore a database with a new name
Optionally, execute the RESTORE FILELISTONLY statement to determine the number and names of the files in the database backup.
Execute the RESTORE DATABASE statement to restore the database backup, specifying:
The new name for the database.
The backup device from where the database backup will be restored.
The NORECOVERY clause if you have transaction log backups to apply after the file backups are restored. Otherwise, specify the RECOVERY clause.
The transaction log backups, if applied, must cover the time when the files were backed up.
The MOVE clause for each file to restore to a new location if the file names already exist. For example, creating a copy of an existing database on the same server for testing purposes may be necessary. In this case, the database files for the original database already exist, and so different file names need to be specified when the database copy is created during the restore operation.
Examples
This example creates a new database called MyNwind2_Test. MyNwind2_Test is a copy of the existing MyNwind2 database that comprises two files: MyNwind2_data and MyNwind2_log. Because the MyNwind2 database already exists, the files in the backup need to be moved during the restore operation. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored.
USE master
GO
-- First determine the number and names of the files in the backup.
-- MyNwind_2 is the name of the backup device.
RESTORE FILELISTONLY
FROM MyNwind_2
-- Restore the files for MyNwind2_Test.
RESTORE DATABASE MyNwind2_Test
FROM MyNwind_2
WITH RECOVERY,
MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'
GO"
July 13, 2003 at 9:20 am
That's the way! The key is the with move to change the file names on disk. It's probably the best way. Alternatives are to detach the db (which takes it out of service) and just copy the mdf, or to do any type of replication where the snapshot will make a copy for you.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply