Thursday, February 3, 2011

Daily Tasks: How Long Since the Last Backup?

Ever wake up in a cold sweat in the middle of the night, worried that a disaster will strike your datacenter—and when you go to recover from backup, you’ll realize you haven’t backed up the database in several days?
Okay, probably not, but do you find yourself searching your backup server every morning to make sure a backup was created last night?  While doing so is a good exercise (you want to make sure your backups are actually there, after all), and if you don’t have too many databases to manage, this may be an easy enough task to accomplish that it isn’t really a nuisance for you.

Scripts for Daily Tasks

One of the things that can make your life as a DBA easier is to write scripts to take care of your daily tasks.  This can make it easy for you to come in on Monday (after a weekend that hopefully did not involve work) and quickly see if there was an issue over the weekend that demands your attention.  Having a script you use to do these tasks can greatly reduce the amount of time you spend these tasks and increase your efficiency in managing large environments.

One script that can really be useful is to check to see that all of your databases have been backed up recently.  This script can be run in any database, and should work in SQL 2005 or higher.  It basically lists the database and the number of days since the last backup (full or differential).  It’s not bulletproof—as noted above, you need to make sure your database backups are actually available on disk.  You need to (as the previous post describes) make sure you can actually restore these backups so there are no surprises when a disaster strikes.  This script just gives a quick-and-dirty look using the backupset table in the msdb database, which keeps track of your backups.

select, DATEDIFF(dd, ISNULL(bs.backup_finish_date, d.create_date), GETDATE()) as 'days'
from    sys.databases d
left join    msdb.dbo.backupset bs
on        bs.database_name =
and    bs.type IN ('D', 'I')
where d.database_id <> 2

The query returns the name of the database and the number of days since the last full (“D”) or differential (“I”) backup—and if it hasn’t been backed up, the number of days since the database was created.

Running this against multiple servers can help you find which databases didn’t get backed up recently, which you can then use that information to investigate why.  Possibly you don’t have that database being backed up frequently enough (or at all). Possibly the backup job failed for some reason.  The purpose of this script is to reduce the amount of time you spend searching through servers, folders, and job histories and get you to fixing your servers’ issues faster.

Thursday, September 16, 2010

Restoration Tests

Are you performing regular backups of your production databases?

  • If you answered "no" then you should probably stop reading here and go update your resume.
  • If you answered "yes" then so far, so good.

Backups are an essential part of any database Disaster Recovery ("DR") plan.  But while taking database backups is absolutely essential, backups alone do not ensure recoverability.

This is why it is equally critical to run regular restoration tests of at least your most critical databases (or, better yet, all of the production databases).

(Here's a great article Mike Walsh (blog | twitter) wrote about this topic).

That said, running regular restoration tests can be daunting without a good script.  I don't know what your environment looks like, but in environments I've worked with, the scenario looks something like:

  • The backup server is on the same network as the restoration-test server, so backups are easy to get at.
  • There's a basic structure to where the database data and log files go, but typically
  • The restoration server uses a different drive-letter than the production databases
  • There are quite a few different files in each database backup
  • Database backups need to be regularly recovered to the restoration-test server
  • DBCC CHECKDB needs to be run against the database, once recovered
  • Results of the restoration and CHECKDB need to be recorded
So, if this aligns closely to your environment, you may find use in a script such as the below, which is designed to handle native SQL Server backups (though it could be pretty easily modified to use a great third party tool such as Quest's LiteSpeed for SQL Server or Red-Gate's SQL Backup, or even to use SQL Server 2008's new Backup Compression capabilities).  


declare @db VARCHAR(100)
, @filename VARCHAR(400)
, @backup VARCHAR(8000)

SET @db = <[db_name], varchar, 'test'>
SET @filename = <[file_name], varchar, 'C:\backups'>

--table to hold backup file info
create table #backups 
(logicalname nvarchar(128)
, physicalname nvarchar(260)
, type char(1)
, filegroupname nvarchar(128)
, size numeric(20,0)
, maxsize numeric(20,0)
, fileid bigint
, createlsn numeric(25,0)
, droplsn numeric(25,0)
, uniqueid uniqueidentifier
, readonlylsn numeric(25,0)
, readwritelsn numeric(25,0)
, backupsizeinbytes bigint
, sourceblocksize int
, filegroupid int
, loggroupguid uniqueidentifier
, differentialbaselsn numeric(25,0)
, differentialbaseguid uniqueidentifier
, isreadonly bit
, ispresent bit)

set @backup = 'RESTORE FILELISTONLY FROM DISK='''+@filename+''''

--Get Files
insert #backups
exec (@backup)

set @backup = 'RESTORE DATABASE '+@db+' FROM DISK='''+@filename+''' WITH ' 

--Move Files
select @backup = @backup + 'MOVE '''+logicalname+''' TO ''M'+substring(physicalname, 2, 400)+''','
from #backups

--Drop Trailing Comma ","
set @backup = substring(@backup, 1, len(@backup)-1)

--Drop Filelist Table
drop table #backups

--print @backup

set @backup = 'dbcc checkdb('+@db+') with no_infomsgs, all_errormsgs'

--print @backup

--Drop DB cmd
set @backup = 'drop database '+@db

--print @backup

So there you go! The above script assumes you're restoring to the M drive, so change the "Move Files" command above to match your server's drive letter. From there, you can either capture the output of the commands and save them (for recording purposes), or check the errorlog.

Let me know what you think in the comments.