Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

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

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: More > 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:

If you know the database naming pattern, identifying the proper database is fairly easy to do

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


Last Update: 11/20/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, November 20, 2012 - 9:41:19 AM - Mathias Msemo Read The Tip

I like it. A simple and nice piece of code.


Wednesday, November 21, 2012 - 3:59:37 AM - Devi Prasad Read The Tip

superb!


Monday, December 03, 2012 - 3:38:50 AM - PJ Read The Tip

I just use Ola Hallengren's excellent solution to ensure all user databases and their logs are backed up



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.