How to Backup and Restore SQL Server Databases Using Windows Server Backup: A Complete Technical Guide


2 views

Windows Server Backup (WSB) leverages Volume Shadow Copy Service (VSS) to create application-consistent backups of SQL Server databases. This works through SQL Server's VSS writer service which ensures transaction consistency during backup operations.

To properly backup SQL Server databases:

1. Open Windows Server Backup
2. Select "Backup Once" or "Backup Schedule"
3. Choose "Custom" backup configuration
4. Under "Items to back up", select:
   - System State
   - SQL Server installation directory (typically C:\Program Files\Microsoft SQL Server)
   - All database files (.mdf, .ldf, .ndf) from their storage locations
5. Enable VSS full backup option

File Selection: You must include:

  • All database files (.mdf - primary data, .ldf - transaction logs)
  • SQL Server system databases (master, model, msdb)
  • SQL Server binary files

Online vs Offline: WSB performs online backups when SQL Server is running through VSS. For offline backups, stop SQL Server service first.

To restore a database:

1. Launch Windows Server Backup
2. Select "Recover" 
3. Choose "Applications" as recovery type
4. Select "SQL Server" as the application
5. Specify recovery destination
6. Complete the wizard

WSB handles incremental backups at the volume level. For SQL Server-specific considerations:

  • Enable "Incremental" backup type in WSB
  • SQL Server VSS writer ensures transaction consistency
  • Subsequent backups will only capture changed blocks

Always verify backups by:

-- Check database integrity after restore
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS;

Recommended practices:

  • Test restores regularly
  • Monitor VSS writer status (vssadmin list writers)
  • Combine with native SQL Server backups for redundancy

Key Microsoft resources:

  • Technet: Windows Server Backup overview
  • SQL Server VSS writer documentation
  • KB articles on application-consistent backups

Windows Server Backup (WSB) leverages Microsoft's Volume Shadow Copy Service (VSS) to create application-consistent backups of SQL Server databases. The key components involved are:

  • SQL Writer Service (sqlwriter.exe)
  • VSS Requestor (wbengine.exe)
  • SQL Server VSS Writer

To properly back up SQL Server databases using WSB:

  1. Prerequisites: 
    # Verify SQL VSS Writer is running
Get-Service -Name "SQLWriter" | Select Status,StartType

# Required permissions:
# - Local Administrators group membership
# - sysadmin role in SQL Server
  2. Backup Configuration: 
    # PowerShell command for scheduled backup
wbadmin enable backup -addtarget:\\backupserver\share -schedule:23:00 -include:C:,D:\SQLData -allCritical -vssFull

For complete database protection:

  • Include all .mdf (primary data files)
  • Include all .ndf (secondary data files)
  • Include all .ldf (transaction log files)
  • Include SQL Server system databases (master, model, msdb)
  • Optionally include FILESTREAM data

The restoration process requires careful sequencing:

# Step 1: Identify available backups
wbadmin get versions

# Step 2: Perform item-level recovery
wbadmin start recovery -version:04/15/2023-09:00 -itemtype:app -items:SQLServer

WSB performs online backups by default through VSS, but consider:

Backup Type Considerations
Online (VSS) Requires SQL Writer service, maintains database availability
Offline Stops SQL services, guarantees consistency but causes downtime

For implementing differential backups:

# Daily command for incremental backups
wbadmin start backup -backupTarget:E: -include:C:\SQLData -allCritical -vssCopy -quiet

# Weekly command for full backups
wbadmin start backup -backupTarget:E: -include:C:\SQLData -allCritical -vssFull -quiet

Essential post-backup checks:

# Check backup logs
Get-WBSummary | Select Result,BackupTime,BackupTarget

# Verify SQL Server consistency
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS;

Frequent problems and solutions:

# Error: VSS_E_WRITERERROR_INCONSISTENTSNAPSHOT
# Solution: Check for database consistency issues before backup

# Error: WB_E_INVALID_VERSION
# Solution: Ensure matching WSB versions between backup and restore servers