Backup Your NAV Database for Testing
From time to time we need to backup a production database in order to create/refresh a testing or development database. But have you been doing it the right way?
What is the right way?
Simply, the right way is to employ the “COPY_ONLY” backup option when creating your backup. The “COPY_ONLY” option was added to SQL Server starting with SQL Server 2005. The reason for using “COPY_ONLY” for non-scheduled backups has to do with how SQL Server manages database restores. To ensure that backups (full, differential or transaction log) are (1) restored in the correct order and (2) are not missing any transactions, backup files contain the first and last LSN that is covered by the backup.
So what’s an LSN? LSN’s are Log Sequence Numbers that are assigned to each transaction that is processed by the server. So by keeping track of what LSN’s and therefore transactions that are contained in a backup, SQL Server can ensure that all backups are restored in the correct order and are complete.
So what does “COPY_ONLY” have to do with LSN’s? Essentially the “COPY_ONLY” option stops SQL Server from breaking the LSN sequence chain so that it appears to the normal backup/restore processes as if the backup never took place.
So why does this matter, what’s the big deal?
This is a big deal because unless you remember to keep your non-“COPY_ONLY” backup and store it with your normal backup files, if you need to restore your database and you don’t have all of the backup files you will get an error message such as this one:
When this happens, you are dead in the water unless you can find that file and therefore your database will not be able to be fully restored and YOU WILL LOSE DATA!
How to take a “COPY_ONLY” Backup
Starting in SQL Server Management Studio 2008, performing the “COPY_ONLY” backup is simply a matter of checking one box…you have probably noticed it but never thought about what it does since “COPY_ONLY” doesn’t really sound like something you would want to do.
If you prefer writing T-SQL (or are on a 2005 SQL Server) the syntax to add “COPY_ONLY” to a backup is simple – just add “WITH COPY_ONLY” to end of your backup command. For example:
The “WITH COPY_ONLY” option is compatible with all other options you normally do such as using compression, etc…
So save yourself some heartache and professional embarrassment and always create your non-scheduled backups using the “COPY_ONLY” option, your users will thank you for it!