Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Category Archives: Backups

I am often called upon to restore a copy of a live database to a development server. Taking a one-off backup in the normal manner would cause problems, as the sequence of LSNs (Log Sequence Numbers) would be disrupted on the production server. This means that subsequent log or differential backups would follow the one-off backup, rather than the file produced from the regular scheduled backup plan. Thankfully there is a solution….

Issuing a backup with the COPY_ONLY option stops the log sequence chain from being broken. Use the option as follows:-

TO DISK = 'E:\Backups\TestRestoreFile.bak'
WITH INIT,  NAME = 'Full Database Backup', COPY_ONLY,  STATS = 10

Unfortunately, it is not possible to select a COPY_ONLY backup through the Management Studio GUI. The backup command has to be issued through T-SQL. It is also not possible to restore through the interface. As you can see from the following screenshot, you can select the backup file, but the backup set will not appear in the list.

To perform the restore, issue the following command, specifying the target location(s) for data and log files. Include REPLACE to overwrite any existing test database. I would also recommend you rename the files so that they now match the name of the test database, rather than the live database.

FROM  DISK = 'E:\Backups\TestRestoreFile.bak'
MOVE 'LiveDB_data' TO 'E:\Data\TestDB.mdf',
MOVE 'LiveDB_log' TO 'E:\Logs\TestDB_log.ldf',

If you now issue log backups on the production server, the LSNs will be in the correct sequence and the COPY_ONLY backup will effectively have been ignored.

Tags: , , , , , , ,

%d bloggers like this: