Previously I wrote an article listing information on SQL Server backups. I want to expand a little on the practical use of the new and useful copy_only backup available in SQL Server 2005 and 2008. You can’t get to copy_only backups/restores through the Management Studio for SQL Server 2005 (you have to use T-SQL) but you can using the new Management Studio for SQL Server 2008.
This is an ideal way to test production db’s on a development server without disrupting your normal backup schedule. In the following example I am doing a full db backup to the production backup drive:
BACKUP DATABASE [my_db]
TO DISK = N'Z:\backup\my_db_copyonly.bak'
NAME = N'my_db-Full Database CopyOnly Backup',
STATS = 10
But when I restore it I would want to be sure that I’m moving the db files to the correct location on my development SQL Server, hence the WITH MOVE subcommands below. I always want it to have a completely new name:
RESTORE DATABASE [my_db_copy]
FROM DISK = N'Z:\backup\my_db_copyonly.bak'
MOVE 'my_db_dat' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copydat.mdf',
MOVE 'my_db_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copylog.ldf',
FILE = 1,
STATS = 10
So now I have an up-to-date version of the production db on my system. Bear in mind that any logins for applications or users that are necessary would need to be recreated to be able work with this new copy. I keep a special login/user script handy and run it to recreate these.