How can I backup an SQL Server database using Windows Server Backup?
Windows Server Backup is not intended as a backup tool for SQL Server.
...However, the design makes it especially well-suited for smaller organizations or individuals who are not IT professionals...
Quote from Windows Server Backup Step-by-Step Guide for Windows Server 2008 section: "Who should use Windows Server Backup?"
The section you quoted is basically saying, that when Windows Server Backup kicks in, it will trigger the VSS feature in Windows. SQL Server will be aware that a Volume Shadow Copy is occurring and with the aid of SQL Server Writer service, will ensure that a copy of the database files (*.mdf, *.ndf, *.ldf) can be performed.
The purpose of the SQL Server Writer service is defined as follows:
When running, Database Engine locks and has exclusive access to the data files. When the SQL Writer Service is not running, backup programs running in Windows do not have access to the data files, and backups must be performed using SQL Server backup.
Use the SQL Writer Service to permit Windows backup programs to copy SQL Server data files while SQL Server is running.
Quote from SQL Writer Service section: "Purpose"
So that is basically all that happens with the Windows Server Backup.
The backups created with Windows Server Backup while SQL Server is running should be consistent, but the transactions not yet written to disk are not in the Volume Shadow Copy. The database snapshot was taken while being ONLINE.
However the MSDN article Snapshot Backups states:
Only the following types of backups can be snapshot backups:
- Full backups
- Partial backups
- File backups
- Differential database backups. These are supported only when the vendor uses the VSS interface.
and additionally:
Except as noted earlier in this topic, snapshot backups are functionally equivalent to the corresponding conventional backups. You can use snapshot backups in restore sequences with non-snapshot full backups, differential backups, and log backups. Like other backups, snapshot backups are tracked in the msdb database, where snapshot backups are identified by backupset.is_snapshot = 1. For more information about msdb, see msdb Database.
SQL Server does not support online restore from a snapshot backup. Restoring a snapshot backup automatically takes the database offline. A piecemeal restore can incorporate snapshot backups, but all the restore sequences are offline restores. For more information about piecemeal restores, see Performing Piecemeal Restores.
To answer your questions:
- Do I just need to backup the .mdf? The .mdf and the .ldf?
- Online or just offline?
- What's the official procedure for restoring SQL Server databases backed up this way?
- Is there anything special to consider when doing incremental/differential backups?
- Where is all of this documented?
Answers:
- Yes, you can do a Windows Server Backup (VSS) copy of the .mdf and .ldf files. The backup should be consistent, but the database will be OFFLINE after a restore.
- If SQL Server is stopped / database is detached / database is OFFLINE then a VSS copy of the .mdf and .ldf files is 100% consistent.
- I'm unsure of how to restore a database (to a point-in-time) that is conducted with Windows Server Backup, because the database is restored in an OFFLINE state. A database in the OFFLINE state can not be restored/recovered any further and bringing the database ONLINE results in a database being considered fully recovered. I would recommend separate SQL Server FULL, DIFF and TLOG Backups to ensure you can perform a database restore to a point-in-time.
- Transaction Logs are not part of the snapshots created with Window Server Backup and should be performed additionally. Differential snapshots are supported by 3rd party vendors as part of the backup sequence, but Windows Server Backup does not seem to be able to perform differential backups. In this case you would also have to perform additional differential backups. (see 3.)
- Different References
- Understanding SQL Server Log Sequence Numbers for Backups
- Log Sequence Numbers and Restore Planning
- How do I map a differential/transaction log backup to its full backup without using MSDB?
- Introduction to Log Sequence Numbers
- Snapshot Backups
- SQL Writer
- Introduction to Backup and Restore Strategies in SQL Server
- Restore and Recovery Overview (SQL Server)
Explained in real-life implementation
In our environment we have a similar situation where VMware is conducting a snapshot and the DBA's are conducting SQL Server dumps with Commvault. The backup history looks like this:
DBNAME TYPE BACKUPSET_NAME IS_SNAPSHOT BACKUP_START_DATE
------- ---- ------------------------- ----------- -----------------------
DB_NAME Full NULL 1 2016-12-10 18:23:59.000
DB_NAME Full CommVault Galaxy Backup 0 2016-12-10 20:07:41.000
DB_NAME Log CommVault Galaxy Backup 0 2016-12-11 06:00:40.000
DB_NAME Full NULL 1 2016-12-11 18:24:00.000
DB_NAME Diff CommVault Galaxy Backup 0 2016-12-11 20:03:38.000
DB_NAME Log CommVault Galaxy Backup 0 2016-12-12 06:02:29.000
DB_NAME Log CommVault Galaxy Backup 0 2016-12-12 07:02:17.000
The VMware snapshot will create an entry in the MSDB database history tables with IS_SNAPSHOT = 1 and FULL for each VMware snapshot that is conducted (daily). The native (well Commvault using native) SQL Server backups are conducted using FULL, DIFF and TLOG backups. These backups are not marked as IS_SNAPSHOT and exist as additional FULL (once a week), DIFF (every other day) and LOG (hourly) entries in the backup tables in the msdb database.
With this setup, we can perform either a restore to the snaphot date-time and then bring the database ONLINE, or we can perform an individual restore to any point-in-time using the "native" SQL Server backups.
You can't. Or at least you probably shouldn't. As far as SQL Server databases are concerned native backups are the supported method.
I think you may be misunderstanding what the TechNet article is saying. The comment you quoted doesn't indicate that you can use Windows Server backup for SQL Server databases. It is actually just highlighting that Windows Server backup utilizes the same Volume Shadow Copy Service that is used by SQL Server. More info: TechNet: SQL Writer Service
Sql backup not just copy data, it truncate the log also. You can find hundreds options in the man page thats will help you, such of compressing the backup file on the fly.
I remember that vss backup was used for copying a freezed Virtual Machine or to snapshotting a lun on a SAN in order to backup a static image, but the backup will be "crash consistent" just like power interruption and not really consistent.
Related
- ServerFault.com, 2010-09-18, SQL Server Backups using Volume Shadow Copy
- ServerFault.com, 2015-07-20, Is it safe to back up SQL Server data directly if using VSS?