How to Identify and Backup the Latest SQL Server Database in a Series

By: K. Brian Kelley   |   Updated: 2012-11-20   |   Comments (3)   |   Related: > Backup

Problem

I have to support a third party application that periodically creates a new database on the fly. This obviously causes issues with our backup mechanisms. The databases have a particular pattern for naming, so I can identify the set of databases, however, I need to make sure I'm always backing up the newest one.  Read this tip to ensure you are backing up your latest database in a series.

Solution

If you know the database naming pattern, identifying the proper database is fairly easy to do. For instance, I've created a set of test databases to simulate the situation:

All three start with Test_ or simply just Test. Therefore, if I want to find the newest database, I simply need to query sys.databases for the name and the creation date. We accomplish that with the following query:

SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
ORDER BY create_date DESC

However, if we're going to build a backup process around identifying the name, we need to get the name out of the query and into a variable. If we put this together for a script, here's what we're looking at:

SET NOCOUNT ON;
DECLARE @NewestDB sysname;

-- Find the newest database based on a pattern search
SET @NewestDB = (
  SELECT TOP 1 [name]
  FROM sys.databases
  WHERE [name] LIKE 'Test%'
  ORDER BY create_date DESC);
  
-- Routine to back up newest DB
-- Enter your code to backup the newest database here

PRINT 'Newest DB [' + @NewestDB + '] backup complete.';

And with the name captured into a variable, it's a simple matter of building a dynamic SQL statement to backup the database and then executing the built statement.

Ensuring the Previous SQL Server Database is Backed Up

If the application is rolling over databases, then it is likely that when the new database is created, you won't have a backup after the latest changes to the just previous database. If we want to make sure it gets backed up, then we need to query to see when the last backup of that database was taken. With that and the creation date of the new database, we can determine if a final backup of the previous database is needed. We'll need a couple of more variables a couple of additional queries, but none of it is too difficult. In the end we're left with the following:

SET NOCOUNT ON;
DECLARE @NewestDB sysname;
DECLARE @NextDB sysname;
DECLARE @LastBackupDate DATETIME;
DECLARE @NewestDBCreationDate DATETIME;

-- Find the newest database based on a pattern search
SET @NewestDB = (
SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
ORDER BY create_date DESC);

-- Find when it was created
SET @NewestDBCreationDate = (
SELECT create_date
FROM sys.databases
WHERE [name] = @NewestDB);

-- Find the next oldest database
SET @NextDB = (
SELECT TOP 1 [name]
FROM sys.databases
WHERE [name] LIKE 'Test%'
AND NOT [name] = @NewestDB
ORDER BY create_date DESC);

-- Determine the last backup date for the next oldest
SET @LastBackupDate = (
SELECT 
BU.backup_finish_date
FROM msdb.dbo.backupset AS BU
WHERE BU.[type] = 'D' 
AND BU.database_name = @NextDB);

IF (@LastBackupDate IS NULL) OR (@LastBackupDate < @NewestDBCreationDate)
BEGIN

 -- Routine to back up older DB
 -- Enter your code to backup the oldest database here

 PRINT 'Older DB [' + @NextDB + '] backup complete.';

END;

-- Routine to back up newest DB
-- Enter your code to backup the newest database here

PRINT 'Newest DB [' + @NewestDB + '] backup complete.';

The reason we check to see if @LastBackupDate is null is in the rare case that we don't have a backup record for that particular database. This could mean that there was never a backup taken or that it was taken long enough ago that it has been deleted from the table. In either case, we want to initiate the backup.

Next Steps

• Read up on how to write scripts to retrieve more backup information.
• Learn how to backup all the databases with one script.
• Understand how to execute SQL Server backups through PowerShell.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips