How to Back Up SQL Server Databases – Part 1
Posted 5/17/2010 at 9:07 AM by Brent OzarWhen Windows administrators and network administrators first start trying to back up SQL Server databases, they don’t understand why they can’t just use normal backup tools. Today, I’ll talk about what’s going on behind the scenes.
Why is is So Hard to Back Up SQL Server Databases?
Windows administrators are used to doing backups by simply backing up the files. After all, if there’s anything useful on the computer, it’s stored in a file. By backing up the files, their work is done.
The difficulties start with open files – files that are currently locked by programs that are reading or writing to them. When you open a Word document on a network drive, and someone else tries to open that same file, they get an error that the file’s already open by someone else. Windows doesn’t have an elegant way of letting multiple people access the same file at the same time if they both might make changes to it.
While SQL Server is running, it keeps the data and log files open so that if anyone runs a query or updates data, SQL Server can dive into the right place to store it with a minimum of waiting time. You could theoretically work around this by using the AUTO_CLOSE switch on databases. This tells SQL Server to automatically close database files when nobody’s using them. Unfortunately, this switch has some severe side effects on performance, and it’s rarely enabled. Therefore, if we use normal file backup tools to back up a SQL Server, it will simply skip the data and log files – which are the most important ones we need to back up!
Can We Use Open File Backup Tools on SQL Server?
Most file backup tools offer an option to back up open files. They’ll just go ahead and copy the data, keeping their fingers crossed that nobody will write to the file during that particular time.
The problem arises when backing up larger files at the same time. SQL Server databases are usually tens of gigabytes in size or much larger, and each database has at least two files – a data file (MDF) and a log file (LDF). They’re both dependent on each other being in sync. If we’re trying to back them up by simply copying the files, it’s crucial that we get both files as of the exact same moment in time.
Open file backups, however, usually work on a file-by-file basis. They’ll first back up one of the two files, trying to catch changes as they happen, and then they’ll back up the second file. The files might be individually consistent – but when used as a pair, they won’t be synchronized. If we’re lucky enough to capture a small database when absolutely no activity is happening, we might be able to restore it just fine.
This is where “accidental” database administrators get themselves into trouble – they test open file backups with a small database on an isolated system. The restore works, the database attaches fine, and they decide to proceed with this approach on progressively larger production systems. The backup software never throws errors because it thinks everything is working fine – each file is successfully backed up.
When disaster strikes and they have to restore, they restore files from the most recent backup, but when they try to attach those databases to SQL Server, SQL says they’re corrupt. They restore older and older backups, trying to find a copy that’s in sync. If they’re lucky, they find a copy – but even then, they lost all the data that was written to the database in the meantime.
What About SAN Snapshot Backups for SQL Server?
Open file backups are slow because it’s hard to move so much data around, but Storage Area Networks (SANs) have the ability to grab very fast snapshots. In a matter of milliseconds, they can make virtual copies of terabytes of data all at once. The secret is that they’re not actually making copies; they’re just tracking all changes AFTER that point to a separate place. They make a note of any new changes that come in.
While these are faster, they’re still not a solution if they’re not perfectly managed. For example, databases often have their data and log files on separate volumes (LUNs in SAN-speak), and the SAN may not be able to take snapshots of two different LUNs at the exact same time. They might take a snapshot of the data drive, and then take a snapshot of the log drive, but if those two aren’t taken simultaneously, we’re right back where we started.
Furthermore, SAN snapshots have a weakness; they’re still inside the SAN. If we’re trying to protect ourselves from SAN failure or from a datacenter failure, we still have to get that data off the SAN as fast as possible. Another common need is to create a copy for development or QA purposes on a different server that isn’t connected to the SAN, or send data offsite for auditors to access. In these cases, we still need to be able to get the SQL Server backed up.
This is where SQL Server native backups come in, and I’ll explain these in my next post.
