Last night while dealing with a full transaction log, it was requested that I restore a database.  I generated the restore statement to a certain  point in time and reviewed the script.  The backup file was being retrieved from a non-standard location and when I went there, the backup file was not present!  I tracked down a job on the dev server which was backing up this DB, restoring it to dev and then deleting the file!  The result of this is that we effectively had no recovery available for the database since that backup was deleted.  How did this happen and how can we avoid this?

It’s a frequent task in SQL to create a back for a developer to use in staging, development or on their desktop.  If they can do with last nights data, you can just give them a copy of the previous day’s backup.  If they need something current, you’ll need to make another backup.  You’ll probably run a statement like this:

BACKUP MYDATABASE TO DISK = 'E:\BACKUPS\MYBACKUP.BAK'

This file has now become a vital part of your recovery for this database.   To recover anything done past this point using transaction log backups made in the future, you’ll need this file.  So you can’t just drag this file off to another server or share.

If you want to move the file around or delete it, use this command: 

BACKUP MYDATABASE TO DISK = '\\SERVER_1\SHARE$\MYBACKUP.BAK' WITH COPY_ONLY

You can back up straight to a network share on another machine and save some time copying files around.  This doesn’t disturb your chain of backups and log backups and lets you do as you please with the file. 

More information on log sequence numbers and how they work can be found here http://msdn.microsoft.com/en-us/library/ms190729.aspx .