kriki: Destroying your backup strategy by making a backup in SQL Server

[Blog bot]

Yes, you read correctly, it is possible to destroy your backup strategy by making a backup.

How is that possible? Making backups is the whole point of a backup strategy!

I explain with an example:

For a server, your backup strategy is to make a full backup during the night and making transaction log backups every 15 minutes during the working hours. (BTW: I hope you don’t just do only 1 (one!) transaction log backup to empty the transaction log file, do you? BTW2: I hope you don’t put the backup-files on the same fysical disks as the database or transaction log files.)

Lets say, you have that setup well enough and all is working fine.

If it happens you need to do a restore of the database to the same server (same DB name or another DB name), SQL Server shows wich backups (a combo of 1 full backup, 0 or 1 differential backups and 0 or more transaction backups) you need to restore. If you confirm, SQL starts restoring all and all goes well if all backup files are found (and not damaged of course).

Now, if you do a full backup at a certain point (why is not important here) and you move the backup somewhere else (or delete it when your finished with it), you just might have destroyed your backup strategy!

WHY?

Lets get back to the restore before: SQL Server will propose the full backup you did (and deleted!) if you want to restore to the last changes in the database. When you start the restore, it will error out because SQL Server did not find your full backup you just deleted! Congratulations, you just destroyed your backup strategy! Actually, it is still possible to restore your database, but you have to do it manually.

How to avoid this problem? It is easy: Make your backups using the "Copy-only backup" toggle on the GUI interface. You don’t see that toggle? It is on the first page. It isn’t!?!? Are using still using SQL 2005? No? You are using SQL 2000???????????? What? You really should think about upgrading!!!!!

Ok, if you are using SQL2000 (or older) forget it. Or better : UPGRADE!!!

If you are using SQL2005, it is possible but you need to do it in TSQL. Generate the TSQL script (there is a nice button to do this.)

It will generate something like this:

 

BACKUP DATABASE [your_database] TO DISK = N’I:your_database.BAK’ WITH NOFORMAT, NOINIT, NAME = N’your_database-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

Now add the "COPY_ONLY" to the command like below and you are ready to go.

 

BACKUP DATABASE [your_database] TO DISK = N’I:your_database.BAK’ WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N’your_database-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

What does this COPY_ONLY do?

Well, not much, it just says SQL to consider this backup as not-existent for your backup-strategy. So when doing a restore, SQL will NEVER propose this backup.



Читать дальше