Restoring a Database: From Copy-Only Backup File


In my earlier post, I had discussed how we can take a Full database backup with Copy-Only option. We can not create a Copy-Only backup using SSMS 2005, as we need to use a T-SQL script for the same. In this post, we are going to discuss how should we restore the database from a copy-only backup file. For this demo, we are going to restore the database SKG_TEST2 on our Dev instance (SQL2K5Dev). To complete this demo, I have already created a Copy_Only backup of SKG_TEST2 database from our default instance.

The syntax to restore the database from a copy-only backup file is as follows:

RESTORE DATABASE [SKG_TEST2]
    FROM DISK = 'D:\Backup\Test\SKG_TEST2_Copy_Only.bak'
WITH MOVE 'SKG_TEST2_Data' TO 'D:\UserDB\DBS01_SQL2K5Dev\Data\SKG_TEST2_Data.MDF',
MOVE 'SKG_TEST2_LOG' TO 'D:\UserDB\DBS01_SQL2K5Dev\Data\SKG_TEST2_Log.LDF'
GO

Once we execute the above code on our dev instance, the database SKG_TEST2 will be restored and we will get the following information message in SSMS.

image

When we expand the Database folder on our dev instance, we will get the following screen:

image

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

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