11

Apparently Windows Server Backup has some support for backup of SQL Server databases:

Ability to recover applications. Windows Server Backup uses VSS functionality that is built into applications like Microsoft® SQL Server® to protect application data. [Source: TechNet]

In addition, people report that Windows Server Backup seems to trigger some sort of backup operation in SQL Server.

However, I fail to find (official) documentation on how exactly to backup SQL Server databases using Windows Server Backup.

  • 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?

(I know how to do backups with SQL Server Maintenance Plans, sqlmaint.exe, T-SQL BACKUP and SQL Server Agent. I'm just interested in the alternative that Windows Server Backup (apparently?) offers.)

  • What version of Windows Server, and what version of SQL server? – pauska Oct 3 '13 at 10:30
  • @pauska: Personally, I'd be interested in Windows Server 2012 R2 and SQL Server 2012. (I deliberately keep the question more general to make it more useful to others.) – Heinzi Oct 3 '13 at 11:32
  • 1
    I have a Windows Server 2008 that is doing a full backup. As far as I can tell it is pulling the databases from SQL Server Express as part of its full backup, since each database says that it was last backed up at the time that that backup runs. This has confused me, since I'm trying to figure out how I've managed to backup these databases, since I can't find a script that's doing it anywhere. I think I will write a script to do it anyway, so that I know it's working and know exactly what I have. – Dave Cousineau Sep 4 '15 at 19:54
5
+50

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:

  1. 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.
  2. If SQL Server is stopped / database is detached / database is OFFLINE then a VSS copy of the .mdf and .ldf files is 100% consistent.
  3. 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.
  4. 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.)
  5. Different References

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.

1

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

  • I know that I shouldn't -- I wouldn't entrust my data on such a poorly documented feature. Still, I'm curious if it is possible, theoretically: SQL Server does provide a VSS writer, and Windows Server Backup is a VSS requestor, so I'll leave the question open for now. Maybe it's just a question of matching those two up (there are, for example, instructions online on how to do this for the Hyper-V VSS writer). – Heinzi Oct 9 '13 at 13:49
  • 1
    Apparently, Windows Server Backup does trigger some kind of SQL server backup, see this quesiton. – Heinzi Dec 13 '16 at 9:59
1

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

0

I think it uses a special backup type called "Snapshot Backup".

From the SQL Server 2008 documentation:

SQL Server snapshot backup is also used by Microsoft Windows Server 2003 Volume Shadow Copy Service (VSS) and by all the backup software and storage software that uses this framework. For more information, see SQL Writer Service.

It is all very unclear to me. Especially if this truncates transaction logs or not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.