Copy only backup

How can a developer get a copy of the database without messing up a potential recovery from a disaster? Using the new COPY_ONLY option. Taking a full backup with this option does not make the new backup a differential base – it does not clear any of the differential bitmaps and basically doesn’t interfere with the regularly scheduled backups. Apart from that, it’s a regular full backup of the database. One thing to bear in mind is that it’s a one-off – you can’t use one of these backups as a differential base, so you can’t take COPY_ONLY differential backups. If you specify COPY_ONLY with DIFFERENTIAL, the option is ignored.

One other cool thing is that you can specify this option for a BACKUP LOG command too. This behaves the same way – it takes a log backup, but does not change the transaction log at all (i.e. it doesn’t make any portion of the log inactive and permit log clearing), and does not form part of the log backup chain. This is useful for doing online file restores without having the necessary backup of the tail of the log affect the log backup chain.

--copy only backup of FILE with init, checksum, verify, and overwrite

BACKUP DATABASE [TestDB] FILEGROUP = N'PRIMARY' TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\Backup\TestDB.bak'

WITH  COPY_ONLY,  RETAINDAYS = 7, NOFORMAT, INIT,  NAME = N'TestDB-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CHECKSUM

GO

RESTORE VERIFYONLY FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\Backup\TestDB.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

GO

--copy only backup with noinit, continue after error

BACKUP DATABASE [TestDB] TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012_2\MSSQL\Backup\TestDB.bak'

WITH NOFORMAT, NOINIT,  NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CONTINUE_AFTER_ERROR