I’m writing this post for myself 3 years ago. I had just graduated college and received my first job offer, Associate BI Developer. I knew nothing about SQL Server as a product, I had just used Management Studio a lot. In this series of posts, labelled by day number, I want to talk about the things that a new Database Administrator would find useful.
Let’s talk about the backups. All databases have a setting called recovery model. As of SQL Server 2016, there are only 3 choices for this setting. Full, simple, and bulk-logged. At the bottom of this post, I’ll provide some useful articles on database recovery models. For now, we need to know why this important.
When you pick your recovery model, you’re making a big decision for SQL Server. The choice of recovery model will decide how much data you can recover from your backup files.
I’m going to demo the different backup tables in my test virtual box. You can create one just like mine (for free!) by following this post.
In this script, I create a backup of the master database, one of the five system databases on SQL Server. This database will exist on every installation of SQL Server.
Here is the script for creating a backup:
USE master
GOBACKUP DATABASE master TO DISK = ‘C:\SQLData\master1.bkp’
The Service Account that your SQL Server uses must have permission to the location.
Now, I’ll show how to find this backup without knowing how the backup was taken. The system database, MSDB, has useful Dynamic Management Views, or DMVs, where you can find this information. The DMV I use to find SQL Server backups is msdb.dbo.backupmediafamily.
Query this DMV with a query like this:
SELECT * FROM msdb.dbo.backupmediafamily
In my environment, this returned two rows. The column I want to see is “physical_device_name” and it says:
C:\SQLData\master1.bkp
If there is a lot of activity on a SQL Server, this table will contain a lot of rows. This is a good place to start looking for backups, and the other tables related to backupmedia and backupsets within MSDB will fill in the other information on native SQL Backups.
So what if there is no information inside this table for your backups? This could mean many things. It could mean that backups are being taken by a third party program that doesn’t update SQL tables when it takes backups. It could also mean that the databases aren’t being backed up. It’s important to look for other reasons before assuming there are no backups, but this is a good place to start.