SQL Server copy_only backup and restore

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'
WITH
NOFORMAT,
INIT,
copy_only,
NAME = N'my_db-Full Database CopyOnly Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

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'
WITH
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,
NOUNLOAD,
REPLACE,
STATS = 10
GO

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.

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , , . Bookmark the permalink.

One Response to SQL Server copy_only backup and restore

  1. hostgator says:

    I will immediately snatch your rss feed as I can’t find your email subscription hyperlink or e-newsletter service. Do you’ve any? Please allow me recognise in order that I may just subscribe. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s