SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Restoring a Copy Only Backup–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a good BOL reference.

The Tests

Here’s what I did. First, I created a table in a database. I often do this and drop in messages to allow me to track the progress of backups and restores. This post follows my progress.

The Backups

Here’s my basic script:

CREATE TABLE logger(msg VARCHAR(200), msgdate DATETIME DEFAULT GETDATE())

INSERT logger (msg) SELECT 'pre full backup'

Next, I made a backup and added a message.

INSERT logger (msg) SELECT 'pre-log backup 1'
BACKUP LOG nba TO disk = 'nba_1.trn'
INSERT logger (msg) SELECT 'log backup 1 complete'

Once this is done, I’m in a state that I expect. A normal full backup, a normal log backup, and some data to help me track where I am.

Now let’s make a copy only backup.

INSERT logger (msg) SELECT 'pre copy-only backup '
BACKUP DATABASE nba TO DISK  = 'nba_copy.bak' WITH COPY_ONLY
INSERT logger (msg) SELECT 'copy-only backup complete'

This now means I have an open log sequence in the first log backup (post full backup) and a few log records since then. Some of these are inside the copy only backup.

Now let’s add more data and make a new, regular, log backup.

INSERT logger (msg) SELECT 'pre-log backup 2'
BACKUP LOG nba TO disk = 'nba_2.trn'
INSERT logger (msg) SELECT 'log backup 2 complete'

It’s at this point that I have this sequence:

  • Full backup
  • Log backup
  • Copy-Only Full backup
  • Log backup

The Restore

What I want to test is can I restore the Copy-Only backup and a log backup? I think I can, so let’s do that. First, restore from the copy-only backup, using the MOVE option.

USE [master]
RESTORE DATABASE [NBA2] 
FROM  DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\nba_copy.bak' 
WITH  FILE = 1,  
      MOVE N'NBA' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2.mdf',  
      MOVE N'NBA_log' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_log.ldf',  
      MOVE N'nba_mo_file1' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo',  
      MOVE N'nba_mo_file2' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo2'
,  NOUNLOAD,  STATS = 5
, NORECOVERY

Tip: Always use NORECOVERY

Now let’s try to restore the log.

RESTORE LOG NBA2 FROM DISK = 'nba_2.trn' WITH NORECOVERY

RESTORE DATABASE nba2 WITH RECOVERY

This works:

2017-12-06 17_57_56-SQLQuery2.sql - (local)_SQL2016.master (PLATO_Steve (63))_ - Microsoft SQL Serve

That should prove things. Let’s check the logger table.

2017-12-06 18_00_45-SQLQuery2.sql - (local)_SQL2016.NBA2 (PLATO_Steve (63))_ - Microsoft SQL Server

That’s what we expect. The final message after log backup2 wasn’t captured in our backup files.

Copy Only Backups

What is a copy only backup? If we check the Copy-Only Backups page, we find that this is a regular backup in and of-itself, but it has the restriction that it cannot be used with differential backups. This also doesn’t change the differential bitmap, so that any differentials that are made ignore this backup and go back to include data changed since the last “normal” full backup.

SQLNewBlogger

Understanding backup and recovery is critical for a data professional. I’d say this is the most important skill, and it’s always worth writing about. Spend a few minutes reviewing scenarios and creating some posts like this to show you understand how the system works.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...