Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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.
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.
- 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.
Last Update: 2012-11-20
About the author
View all my tips