Database Backups vs Server Backups in SQL Server – Key Differences

backupsql server

At my current job, I recently discovered that our MS SQL database servers had no backup devices set up for them. When I asked other IT members about this, the response was that the servers themselves were being backed up regularly, including the database files, so taking SQL server backups would be redundant.

I'm sure this is a very elementary question, but it got me thinking: why does MS SQL server, as well as other RDBMS systems, have their own backup system? Most other applications don't have this. My first thought is that only the RDBMS can perform integrity checks on the database files.

What other reasons are there? I think we need to change our backup and restore procedures, but I need to make sure I have a solid case for doing so, since it will require additional investments.

Best Answer

If SQL Server is running, the "system" backups will either (a) not include the SQL Server files, or (b) they may not be in a consistent state1.

Ensure SQL Server native backups are taking place on a regular basis, and test the restore procedure.

Also, note the lack of backup devices does not necessarily mean there are no SQL Server native backups taking place. You need to look at the dbo.backuphistory table in the msdb database to see if backups have taken place. One way to look at the backup history is to run the following query:

/*
    You can use this variable to either (a) show all database backup
    history, or just history for a single database.
    If you leave it as-is, it will display history for all databases
*/
DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); 
SET @DBName = NULL; 

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = 
        CASE bs.type 
        WHEN 'D' THEN 'Database' 
        WHEN 'L' THEN 'Log' 
        ELSE '[unknown]' 
        END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf 
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = @DBName
    OR @DBName IS NULL
ORDER BY bs.backup_start_date DESC;

1 - see https://msdn.microsoft.com/en-us/library/ms175536.aspx for details about running the SQL Server VSS Writer service to ensure consistent backups when running backups outside of SQL Server.

Related Question