A Common Misconception About SQL Server Backups

The misconception about SQL Server backups: file-level backups are the same thing or as good as a native SQL Server backup of the database.

I find there is a common misconception about SQL Server backups, and I find it when I ask, “When were these databases last backed up?” In fact, I ask myself this question every time I connect to a SQL Server instance for the first time.

Then I go and find the answer myself. There are a bunch of ways to get this info, but I find querying MSDB the easiest:

SELECT d.name database_name
 
,MAX(backup_finish_date) last_backup_date
 
,CURRENT_TIMESTAMP right_now
 
FROM sys.databases d LEFT join msdb.dbo.backupset b ON d.name = b.database_name and b.type = 'D' -–ONLY concerned about FULLs RIGHT now
 
GROUP BY d.name

That’s not good.

Now I know that I need to dig deeper into the backup strategy in place. This typically starts with firing off a couple of questions to whomever is responsible for the database.

  1. Did you know that we’re not backing up (insert database name here)?
  2. Is this intentional?

During the conversation that follows, it sometimes comes to light that the database owner does have backups configured for the database in question, or at least they think they do. This is where the misconception about SQL Server backups comes in: that file-level backups of the database files are the same thing or as good as a native SQL Server backup of the database. This  simply isn’t true.

The Key Differences

While they both have their uses and can definitely be used in conjunction with one another, you need to have native SQL backups of your databases.

Why? There are dozens of reasons, but here are the two I bring up the most:

  1. Transaction safety/consistency: Native backups maintain ACID compliance while file-level backups may capture transactions in-flight.
  2. RTO/RPO: Native backups, when configured correctly, make Point-in-time recovery possible. You don’t get this with file-level backups.

This isn’t to say that you can’t or shouldn’t use a third party utility to perform these backups; some of those utilities integrate really well with SQL Server. You just need to know what is being backed up and how to verify. This is where MSDB comes in again. Even third party utilities will generate a record when they perform a native SQL Server backup:

That’s better.

So in sum, file-level backups are great. Just make sure you are also performing and testing native backups. And don’t forget your system databases!

Please share this
Tags
#microsftcertifedmaster #microsftpartner #parallelism #azure #SQLAB #SQlatino #SQlatinoamerica #sqldatabase #sqldeveloper #SQLgroupie #sqlimer #sqlimerbymay #sqlinjection #sqlinternals #sqlite #sqlite3 #SQLLearning #SQLMagazine #sqlmanagementstudio #sqlmanager #Sqlmap #sqlrun #sqlsaturday2017 #sqlsatvienna #sqlserver #SQLserver2012 #sqlserver2014 #sqlserver2017 #sqlserver2022 #SQLServeronLinux #SQLsolutionsgroup #SQLTraining #SQLYog Events Extended Events learning SQL SQL Administration SQL Internals SQLPASS SQLSaturday SQL Saturday SQL Server SSG SSIS T-SQL
Picture of Kyle Burwell

Kyle Burwell

Kyle Burwell resides in Montana and has been working with SQL Server since 2007. The majority of his experience has been in healthcare IT, where he has held various roles working with the entire Microsoft Data Platform, primarily as a Production DBA. Kyle enjoys spending time with his wife, usually watching one of their four kids competing in various sporting events. When not in front of a computer or out with his family, Kyle can usually be found in his woodshop building furniture.

Leave a Reply

Related Articles