Copying a database on SQL Server 2005 using Backup and Restore
November 7, 2007
I always forget to do this, so this is a reminder for me. Hope it comes in handy for others too.One of the things I do quite often, but obviously not enough, is to create a new database based on an existing one. I've tried various methods over the years, but backup and restore seems to work reliably and without too many headaches, and it doesn't involve taking the database offline either.So, for instance to create a new database named JaneDallawayTest from and existing database called JaneTest I would do the following: Backup JaneTest and store the resultant file somewhere I can get hold of it from Then restore that backup to a new database named JaneDallawayTest by executing the following SQL: RESTORE DATABASE [JaneDallawayTest] FROM disk = N'JaneTestBackup.bak' WITH FILE = 1 , move N'JaneTest' TO N'G:\SQLData\JaneDallawayTest.mdf' , move N'JaneTest_log' TO N'G:\SQLData\JaneDallawayTest_log.ldf' , nounload , stats = 10 , replace GOAll that is left to do is to update the logical name for the new database in the Database Properties dialog.
comments powered by Disqus