If you’re running a SQL Server and you care about the data held in it, you should already be backing up the databases. But it’s always surprising the number of times you encounter business-critical databases not being backed up as a DBA!

When given the opportunity to look at a new SQL Server instance one of the first scripts I would run is to check when the databases were last backed up. I’m likely being asked to look at this server because there’s an issue. Issues need fixing, and before any of my changes are executed I need a point-in-time revert back to in-case I make things worse.

This blog post is to note a script that’ll return last Full, Differential, and Log backups for all databases on a SQL Server instance.

Get Last Backup Times in SQL Server

-- Show last backups on all databases
SELECT 
  ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model], 
    MAX(CASE WHEN [type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
    MAX(CASE WHEN [type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
    MAX(CASE WHEN [type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup]
FROM 
  sys.databases AS d WITH (NOLOCK)
  LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
  ON bs.[database_name] = d.[name] 
  AND bs.backup_finish_date > GETDATE()- 30
WHERE d.name <> N'tempdb'
GROUP BY ISNULL(d.[name], bs.[database_name]), d.recovery_model_desc, d.log_reuse_wait_desc, d.[name] 
ORDER BY d.recovery_model_desc, d.[name] OPTION (RECOMPILE);

The above shows my simple wee test database ‘blah’ is the only database that has had a backup on this instance.

Backing up a database is something I’ve always found easier done within a query window. There’s not much to it;

BACKUP DATABASE [DATABASENAME] 
	TO DISK = N'e:\directory\databasename_backupdate.bak' 
		WITH COMPRESSION, STATS

That’s what I’m used to writing. I’ve no need to worry about the copy_only option these days, or check SQL Server Editions to see if I can use compression. Good times that was. Measuring available disk space against estimated backup sizes, and sometimes even adding temporary virtual disks to the server to accommodate the backup file size. The SQL statement for backups is simple… but there’s a lot to consider when performing a one. It just depends on the restrictions of the servers you are working with really.

Below is a simple run-through for backing up a database using the Back Up Database Wizard. As always, have a look at the manual first.

How to Backup a SQL Server Database

1. Right click your database, select Tasks and click Back Up…

2. In the General tab of the prompted window you’ll see it’s already set-up for a Full Backup on AdventureWorks which is using the Simple Recovery model.

3. I’ll remove the destination file and move it somewhere else.

The directory has been picked up by my chosen Database Settings. Type the new backup file name here, remembering the .bak at the end.

4. Over to the Media Options tab, there’s a few options for consideration. Such as verifying the backup or performing checksums. You might think that verifying backups with automatic offsite restores works for you. Or, maybe you’re happy relying on the VM backups? As the importance of your database backups grows, the deeper it goes. I’m leaving those options unchecked as I don’t need them, and all other options are set as default.

5. In the Backup Options tab, I’m leaving all options set to their defaults. No backup expiry or encryption, and I’m going to trust I’m using compression as default on my SQL Server.

6. Click the Script button at the top of the window to see the generated SQL code behind this backup operation.

7. Some of the code contained within the statement is unnecessary, but it’s doing the same thing anyway. We can run this command or click OK on the Wizard.

8. And when you’re done…

You have a backup of your database!