SQL Server Performance Forum – Threads Archive
Restoring From Developement to Production
I have created a development database called CustomerRelationsDev and I want to restore a copy of my production database to it, and it is called CustomerRelations. I made a backup of CustomerRelations. How do I get CustomerRelationsDev to Restore from my production backup. I have tried changing file before and after backup is complete and I keep getting errors upon trying to restore it. Can you help.What are the errors that you are getting??? I would assume you haven’t used WITH MOVE. See the following from Books Online:
quote:
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:MyDataMyNwind2_Test_data.mdf’,
MOVE ‘MyNwind2_log’ TO ‘D:MyDataMyNwind2_Test_log.ldf’
GO
See Also
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:MyDataMyNwind2_Test_data.mdf’,
MOVE ‘MyNwind2_log’ TO ‘D:MyDataMyNwind2_Test_log.ldf’
GO
See Also
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>