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.
When we expand the Database folder on our dev instance, we will get the following screen: