4

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.

  • 2
    SQL predates major adoption of virtualization software and is still commonly installed on bare metal systems. Additionally, as Max Vernon answered, most system backups don't get the SQL Databases in a consistent state. They also won't allow you to do the more advanced backup and restore options. – Jonathan Fite Nov 3 '15 at 15:20
4

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.

  • 1
    Ok - I've now learned that we are doing SQL backups, but they're being done through the VSS provider, which our backup software uses (Dell AppAssure). This script will still come in handy, though. – DanM Nov 3 '15 at 17:27
  • Glad that helped! If you are the DBA charged with disaster recovery, I strongly suggest you get very intimate with that product and learn how to, and then practice, restoring from Dell AppAssure. – Max Vernon Nov 3 '15 at 17:41
  • 1
    Max, i am confused by your statement. You said that the backups would be inconsistent. This is not completely true if you back up with vss as long as you have the sql writer service enabled. I think it does vary only if they are doing file level backups. technet.microsoft.com/en-us/library/ee923636(v=ws.10).aspx – James Rhoat Sep 25 '16 at 20:34
  • @SQLDataInTraining - you're correct in that the SQL Server VSS Writer can provide a consistent backup copy of the SQL Server data files. However, that service does not allow Transaction Log backups (see the article I linked), and generally makes SQL Server disaster recovery point objectives less manageable since transaction logs cannot be restored using point-in-time-recovery. – Max Vernon Sep 26 '16 at 14:13
  • I've clarified my answer to show the SQL Server VSS Writer details. – Max Vernon Sep 26 '16 at 14:14
0

Most server backups can effectively take the place of a 'Simple' backup solution where you only want a FULL backup of your database to be done,

The database backups in 'FULL' mode will allow you do recover the database right up to the point of failure (if you do a tail log backup)

Which means you can end up losing next to nothing, or if there was a problem introduced into the Database you can do a point in time restore and restore the database to just before the issue occurred (say someone decided to truncate a few tables at 13:27 you can restore the database and then run the transaction log up to 13:26 and you still have all of the data there.)

We currently run both, the Server backup gets the machine back up and running, but the SQL server backups are used to get the database back up and running to the most recent point possible

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.