Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure: Determine last database backup

You have dozens or hundreds of SQL Servers to administer and you just want to know when the last backup was taken for each database on each instance. This stored procedure will help.

 
An experienced DBA understands the meaning of backups.

Say a project manager calls you just as you are about to leave the office to spend a nice, quiet evening with your wife or husband. "Help! We just dropped our main table in production. It was a mistake! We need to recover it ASAP to the latest version we have ...."

Your blood has gone from your face to your feet and your whole existence is wondering: Do I really have a backup for this database? When was the last time I checked the maintenance plans? What would happen if I couldn't find the right backup?

Imagine that you have more than 200 SQL Server instances to administer, and you simply want to know when the last backup was taken for each database on each instance so you can anticipate if there will be trouble. You have maintenance plans and you are sending yourself e-mail messages on failure, but are you sure you have maintenance plans are in place for every new database? Are you sure the backup frequency is correct?

Here is a small stored procedure to help you verify such backup information.

TABLE OF CONTENTS
   p_SelectLastDatabaseBackups
   Stored procedure results
   How to run the stored procedure

 
p_SelectLastDatabaseBackups

 Create proc p_SelectLastDatabaseBackup as select max(isnull(datediff
(dd,b.backup_start_date,getdate()),0)) as 'Number of Days since last
backup', b.type as 'Backup type (D-database,L-log)', b.backup_size,
d.name as database_name from master..sysdatabases d with (nolock) left
join msdb..backupset b with (nolock) on d.name = b.database_name and b.
backup_start_date = (select max(backup_start_date) from msdb..backupset
b2 where b.database_name = b2.database_name and b2.type = 'D') where
d.name != 'tempdb' group by d.name, b.type, b.backup_size union all
select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as
'Number of Days since last backup', b.type as 'Backup type
(D-database,L-log)', b.backup_size, d.name as database_name from
master..sysdatabases d with (nolock) join msdb..backupset b with
(nolock) on d.name = b.database_name and b.backup_start_date =
(select max(backup_start_date) from msdb..backupset b2 where
b.database_name = b2.database_name and b2.type = 'L') group by
d.name, b.type, b.backup_size orderby d.name,b.type -- optional

 
Stored procedure results

Number of days since last backup Backup type (D-database, L-log) Backup_size Database_name
104 D 12664832 My_database1
3 L 84480 My_database1
105 D 2705920 Northwind
79 L 12345 Master
79 D 1231 Msdb
50 D 11397120 My_database2
49 D 11261440 My_database3
0 NULL NULL NoBackupsDatabase *

Note that the last database was never backed up!

You can play with the given query as it suits you. For example, if you add the HAVING clause, you can perform such tasks as:

  1. Querying backups only when the last one ran more than X days ago.
  2. Querying backups only when the backup size is more than Y.
  3. Capturing all backup information for reports.

 
How to run the stored procedure

You can run this query on each SQL Server instance. There are several ways to do so, including:

    1. Creating one or several DTS packages that connect to each instance, running the script and copying the results to the central server.
    2. Running the script on each instance: running a local job and sending e-mail message with the results.
    3. Building a program that connects to all the known SQL Server instances (using SQL-DMO or creating a list in a table or in a file), running the script and centralizing the data.

Tip: How to check backups in all the other known instances from one central database

Say you want to run a stored procedure only once and get the backup information from all existing database on all the known SQL Serverinstances on the network. How do you do that? Watch this:

As a DBA, I like to code things in my natural habitat. I feel more comfortable doing that. And I like to run this stored procedure from a central database. Seems impossible? Let's see how you can do that.

Step 1: Choose a SQL Server instance with a central database that will collect all of the backup information. All of the following steps must run on this central machine, SQL instance or database accordingly.

Step 2: We can use the OSQL –L command to create the list of SQL Server instances on the network. Books Online says that the -L option "Lists the locally configured servers and the names of the servers broadcasting on the network."

Run OSQL –L once from the Command Line and check the list:

  • If you don't see all the servers, you should define the missing ones in the "Client Network Utility" as an Alias.
  • If you see old servers, try to delete their entry from the aliases in the "Client Network Utility."

To run Client Network Utility: Go to Start --> Programs --> Microsoft SQL Server --> Client Network Utility. The "Server alias" is the logical name by which the client recognizes the server. The "Server Name" is the name of the instance or its network address. When you run a query in SQL Server, you always do so from a client tool. If you add an alias where your client is running, you can use it to impersonate a server.

The following offers an example:

If I will connect to Michelle now from the machine where this Alias was defined, I will connect to my (local) SQL Server instance.

Step 3: We also have to make sure that we have common access to the instances on the list either by:

    1. Windows Authentication mode (recommended).
    2. SQL Server Authentication mode (not recommended).

With SQL Server authentication mode, you have to hard code the password. In my example I use (of course) the Windows Authentication mode.

Step 4: Let's create the table that will hold the backup information (you can remove or add columns to suit your requirements).

-------------------------------------------------------------------------
Create a centralized table for backup data (run this only once)
---------------------------------------------------------------------------

 if exists (select 1 from sysobjects where type = 'U' and name =
'CentralTableForBackupInfo') drop table CentralTableForBackupInfo
go create table CentralTableForBackupInfo (InstanceName varchar(500),
DaysFromLastBackup int, BackupType char(2), BackupSizeKB bigint,
DatabaseName varchar(200)) go


Step 5
: Run the following script/SP on a regular basis from the central database (i.e: run a job from SQL SERVER Agent):

 CREATE procedure p_SelectAllServersLastDatabaseBackup as
--Optional set nocount on declare
@InstanceName varchar(400), @cmd varchar(4000)
-- holds the command to be run on each server
@cmd2 varchar(4000) -- runs the command on the SQL SERVER
instance with OSQLS (xp_cmdshell)

--------------------------------------------------------------------------
Fetch all SQL SERVER known instances with OSQL -L
---------------------------------------------------------------------------

 Create table #tmp (InstanceName varchar(1000)) insert into #tmp
Exec master..xp_cmdshell 'osql -L' delete from #tmp where
InstanceName is null or InstanceName like 'Servers:%' select * from
#tmp

--------------------------------------------------------------------------
If the current server is not connected to the network, the string "-- NONE --" will be the result from OSQL -L. There is no meaning for this script to run if there are no other servers.
--------------------------------------------------------------------------

 if exists (select 1 from #tmp where InstanceName like '%-- NONE --%')
begin print ('Please connect to the LAN - no servers were encountered!')
return end

--------------------------------------------------------------------------
For each server run the script and insert data into a centralized table
--------------------------------------------------------------------------

 select @cmd = 'select convert(varchar(40),max(isnull(datediff
(dd,b.backup_start_date,getdate()),0))) as Maxd,'+ 'b.type,convert
(varchar(40),b.backup_size) as backup_size,d.name ' + 'into #t from
master..sysdatabases d with (nolock) ' + 'left join msdb..backupset b
with (nolock) on d.name = b.database_name ' + 'where b.backup_start_date =
(select max(backup_start_date) from msdb..backupset b2 where
b.database_name = b2.database_name and b2.type = ''D'') ' 'group by d.name,
b.type, b backup_size ' + 'union all ' + 'select convert(varchar(40),
max(isnull(datediff(dd,b.backup_start_date,getdate()),0))) as Maxd,'+
'b.type,convert(varchar(40),b.backup_size) as backup_size,d.name ' +
'from master..sysdatabases d with (nolock)' + 'left join msdb..backupset b
with (nolock) on d.name = b.database_name ' + 'where b.backup_start_date =
(select max(backup_start_date) ' + 'from msdb..backupset b2 where
b.database_name = b2.database_name and b2.type = ''L'') ' +
'group by d.name, b.type, b.backup_size; ' + -- Since I am getting
results from xp_cmdshell and OSQL - The results are returned in one line
(no columns) -- This is why I separate the values with '@^1^@','@^2^@'
and so on, so I can later substring the values -- easily: 'select
''@^1^@''+Maxd+''@^2^@''+type+''@^3^@''+backup_size+''@^4^@''+name+
''@^5^@'' from #t' create table #tmp2 (a varchar(8000)) declare curs_srvr
cursor for select rtrim(ltrim(InstanceName)) from #tmp open curs_srvr fetch
curs_srvr into @InstanceName WHILE @@FETCH_STATUS = 0 begin select @cmd2 =
'osql -E -S"' + @InstanceName + '" -Q "' + @cmd + '"' insert into #tmp2
(a) exec master..xp_cmdshell @cmd2 delete from #tmp2 where a not like
'%@^_^@%' or a is null update #tmp2 set a = rtrim(ltrim(a)) insert into
CentralTableForBackupInfo (InstanceName, DaysFromLastBackup,BackupType,
BackupSizeKB, DatabaseName) select
@InstanceName, substring(a,charindex('@^1^@',a)+5,charindex('@^2^@',a)
-charindex
('@^1^@',a)-5), substring(a,charindex('@^2^@',a)+5,charindex('@^3^@',a)
-charindex
('@^2^@',a)-5), substring(a,charindex('@^3^@',a)+5,charindex('@^4^@',a)
-charindex
('@^3^@',a)-5), substring(a,charindex('@^4^@',a)+5,charindex('@^5^@',a)
-charindex
('@^4^@',a)-5) from #tmp2 truncate table #tmp2 fetch curs_srvr into
@InstanceName end deallocate curs_srvr GO

Step 6: The table CentralTableForBackupInfo will look something like this:

After executing the last Stored Procedure (p_SelectAllServersLastDatabaseBackup), the table CentralTableForBackupInfo will look something like this:

InstanceName DaysFromLastBackup Backup Type BackupSizeKB DatabaseName
(local) 104 D 12664832 My_database1
(local) 3 L 84480 My_database1
(local) 105 D 2705920 Northwind
(local) 70 L 12345 master
(local) 0 NULL NULL msdb
(local) 50 D 11397120 My_database2
(local) 49 D 11261440 My_database3
Remote1 0 D 12664899 My_database1
Remote1 0 L 84420 My_database1
Remote1 13 D 98665 My_database6
Remote1 0 D 1232 master

Conclusion

It is a good practice to verify on a regular basis that you have all the backups to avoid unnecessary problems in the future. I know that running this small utility has saved me from disasters many times.

ABOUT THE AUTHOR
Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an information technology consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. She She has worked exclusively with SQL Server for the last 10 years, consulting for a diverse group of clients. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.

Updates made to the above tip

I got the following comments from the reader Steve H. (thank you Steve!), and I've decided to make some changes to my initial document. The remarks were:

"1. You may want to include Type = 'I' to include Differentials if they are being used.
2. I had to fight the query string being passed to #tmp2 to strip out spaces because I was getting an error, Input Query String too long. But once I got that figured out, it ran fine.
3. I saw was that this job doesn't clean up old data, so every time it runs it just adds another set of rows for the server. You may want to mention that you'll want to truncate the CentralTableForBackupInfo at the beginning of each run.
4. I don't use the OSQL –L much. It is too client dependent for my tastes. We generally use a Configuration Management Database that has all of the servers under our control. We have about 1,200, so obviously, I'm not setting them all up in EM or the Client Network Utility."

So I changed the original SP to select the max value for each type (solving the First mentioned problem):

 Create proc p_SelectLastDatabaseBackup As select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'Number of Days since last backup', b.type as 'Backup type (D-database,L-log)', b.backup_size, d.name as database_name from master..sysdatabases d with (nolock) left join msdb..backupset b with (nolock) on d.name = b.database_name and b.backup_start_date = (select max(backup_start_date) from msdb..backupset b2 where b.database_name = b2.database_name and b2.type = b.type) where d.name != 'tempdb' group by d.name, b.type, b.backup_size

I also changed the second stored procedure accordingly:

a. Changed the basic query as above (this will also solve the problem of the error that occurs due to "Input Query String too long" in #tmp2).
b. Added an input parameter to get number of days for keeping old data in the CentralTableForBackupInfo table (default = 0 – don't delete). In order to be able to delete the old data, I had to add a Date column to the table and an index – for performance:

 Alter Table CentralTableForBackupInfo add InsertDate smalldatetime default getdate() NOT NULL go Create clustered index idx_CentralTableForBackupInfo_InserDate on CentralTableForBackupInfo (InsertDate) Go

So here it is:

 CREATE procedure p_SelectAllServersLastDatabaseBackup @p_NumOfDays smallint = 0 As set nocount on declare @InstanceName varchar(400), @cmd varchar(4000), -- holds the command to be run on each server @cmd2 varchar(4000) -- runs the command on the SQL SERVER -- instance with OSQLS (xp_cmdshell) If @p_NumOfDays > 0 Delete from CentralTableForBackupInfo where InsertDate <= dateadd(dd,(-1)*@p_NumOfDays,getdate())

-----------------------------------------------------------------------------------------------
Fetch all SQL SERVER known instances with OSQL -L
-----------------------------------------------------------------------------------------------------

 Create table #tmp (InstanceName varchar(1000)) insert into #tmp Exec master..xp_cmdshell 'osql -L' delete from #tmp where InstanceName is null or InstanceName like 'Servers:%'

-----------------------------------------------------------------------------------------------
If the current server is not connected to the network, the string "-- NONE --"
will be the result from OSQL -L. There is no meaning for this script to run if
there are no other servers.
-----------------------------------------------------------------------------------------------------

 if exists (select 1 from #tmp where InstanceName like '%-- NONE --%') begin print ('Please connect to the LAN - no servers were encountered!') return end

-----------------------------------------------------------------------------------------------
For each server - run the script and insert data into a centralized table
-----------------------------------------------------------------------------------------------------

 select @cmd = 'select convert(varchar(40), max(isnull(datediff(dd,b.backup_start_date ,getdate()),0))) as Maxd,'+ 'b.type,convert(varchar(40),b.backup_size) as backup_size,d.name ' + 'into #t from master..sysdatabases d with (nolock) ' + 'left join msdb..backupset b with (nolock) on d.name = b.database_name ' + 'where b.backup_start_date = (select max(backup_start_date) from msdb.. backupset b2 where b.database_name = b2.database_name and b2.type = b.type) ' + 'group by d.name, b.type, b.backup_size ; ' + -- Since I am getting results from xp_cmdshell and OSQL - The results are returned in one line (no columns) -- This is why I separate the values with '@^1^@','@^2^@' and so on, so I can later substring the values -- easily: 'select ''@^1^@''+Maxd+''@^2^@''+type+''@^3^@''+backup_size+''@^4^@''+name+''@^5^@'' from #t' create table #tmp2 (a varchar(8000)) declare curs_srvr cursor for select rtrim(ltrim(InstanceName)) from #tmp open curs_srvr fetch curs_srvr into @InstanceName WHILE @@FETCH_STATUS = 0 begin select @cmd2 = 'osql -E -S"' + @InstanceName + '" -Q "' + @cmd + '"' insert into #tmp2 (a) exec master..xp_cmdshell @cmd2 delete from #tmp2 where a not like '%@^_^@%' or a is null update #tmp2 set a = rtrim(ltrim(a)) insert into CentralTableForBackupInfo (InstanceName, DaysFromLastBackup,BackupType, BackupSizeKB, DatabaseName) select @InstanceName, substring(a,charindex('@^1^@',a)+5,charindex('@^2^@',a)-charindex('@^1^@',a)-5), substring(a,charindex('@^2^@',a)+5,charindex('@^3^@',a)-charindex('@^2^@',a)-5), substring(a,charindex('@^3^@',a)+5,charindex('@^4^@',a)-charindex('@^3^@',a)-5), substring(a,charindex('@^4^@',a)+5,charindex('@^5^@',a)-charindex('@^4^@',a)-5) from #tmp2 truncate table #tmp2 fetch curs_srvr into @InstanceName end deallocate curs_srvr GO

If you manage your instances name in a central table, this is even better, so just take out the #tmp table and use yours instead. The SP will then look like that:

 CREATE procedure p_SelectAllServersLastDatabaseBackup @p_NumOfDays smallint = 0 As set nocount on declare @InstanceName varchar(400), @cmd varchar(4000), -- holds the command to be run on each server @cmd2 varchar(4000) -- runs the command on the SQL SERVER -- instance with OSQLS (xp_cmdshell) If @p_NumOfDays > 0 Delete from CentralTableForBackupInfo where InsertDate <= dateadd(dd,(-1)*@p_NumOfDays,getdate())

-----------------------------------------------------------------------------------------------
For each server - run the script and insert data into a centralized table
-----------------------------------------------------------------------------------------------------

 select @cmd = 'select convert(varchar(40),max(isnull (datediff(dd,b.backup_start_date ,getdate()),0))) as Maxd,'+ 'b.type,convert(varchar(40),b.backup_size) as backup_size,d.name ' + 'into #t from master..sysdatabases d with (nolock) ' + 'left join msdb..backupset b with (nolock) on d.name = b.database_name ' + 'where b.backup_start_date = (select max(backup_start_date) from msdb.. backupset b2 where b.database_name = b2.database_name and b2.type = b.type) ' + 'group by d.name, b.type, b.backup_size ; ' + -- Since I am getting results from xp_cmdshell and OSQL - The results are returned in one line (no columns) -- This is why I separate the values with '@^1^@','@^2^@' and so on, so I can later substring the values -- easily: 'select ''@^1^@''+Maxd+''@^2^@''+type+''@^3^@''+backup_size+''@^4^@''+name+''@^5^@'' from #t' create table #tmp2 (a varchar(8000)) declare curs_srvr cursor for select rtrim(ltrim(
 
  )) from 
  
    open curs_srvr fetch curs_srvr into @InstanceName WHILE @@FETCH_STATUS = 0 begin select @cmd2 = 'osql -E -S"' + @InstanceName + '" -Q "' + @cmd + '"' insert into #tmp2 (a) exec master..xp_cmdshell @cmd2 delete from #tmp2 where a not like '%@^_^@%' or a is null update #tmp2 set a = rtrim(ltrim(a)) insert into CentralTableForBackupInfo (InstanceName, DaysFromLastBackup,BackupType, BackupSizeKB, DatabaseName) select @InstanceName, substring(a,charindex('@^1^@',a)+5,charindex('@^2^@',a)-charindex('@^1^@',a)-5), substring(a,charindex('@^2^@',a)+5,charindex('@^3^@',a)-charindex('@^2^@',a)-5), substring(a,charindex('@^3^@',a)+5,charindex('@^4^@',a)-charindex('@^3^@',a)-5), substring(a,charindex('@^4^@',a)+5,charindex('@^5^@',a)-charindex('@^4^@',a)-5) from #tmp2 truncate table #tmp2 fetch curs_srvr into @InstanceName end deallocate curs_srvr GO

  
 


 

Next Steps

DATEADD and DATEDIFF can be used to calculate SQL Server datetime values

This was last published in April 2006

Dig Deeper on SQL Server Stored Procedures

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close