You are here: Home > Backups
Archive | Backups RSS feed for this section

Using Last Backup Date to Make Sure You get a Full when You need one, and a Differential when you Don't

by on August 17, 2009

Today I was glancing at once of my servers and noticed the backup job was running later than normal. I haven’t been working with this server for long, so I glanced to check where the backup was writing to and checked the output directory. I found that a differential backup was being written, and that the differential backup from the day before was much larger than normal.

Having quick backup times on weekdays on this instance and also saving space on the backup server where possible are both priorities, so the backup job for this server was set to do a full backup on Saturday, and a differential backup every other day of the week.

However, there was a production incident on this instance on Saturday, and the backup job was canceled by the person on call. I didn’t receive a monitoring ticket (since it was a cancellation it didn’t throw an alert), and the person on call must not have checked the backup schedule or thought to mention it.

So today I thought about it a bit, and then recalled that there’s a way to check the last backup time. I wanted to use this so I could have the job do a check as to when the last successful full backup time was, and if it was long enough, to do a full backup no matter what. On this particular database, after more than a week the differentials get a bit out of hand and we need to get in a full– ideally the job would be smart enough to realize this on Sunday if the run on Saturday has an issue.

I’ve even written a script that checked last backup time before! But I thought, “Oh, this must be easy, I’ll just check the usual places for the last full backup date.”

And so I did. Since it shows on the database properties in management studio, it seemed like it must be someplace obvious. Here are some places that last backup time is not listed:

  • sys.databases
  • the databasepropertyfunction
  • the databasepropertyex function

Where is it? Well I finally dug it out of my existing script, and my memory. It’s in msdb, in the backupset table. And once I looked at the existing script, I was glad I did. I think I may brush it up and use it pretty soon– it does a check for last successfull backup time across multiple servers. That can be pretty handy to audit, particularly when you’re taking on responsibility for new servers and want to do a quick high level inventory. (Of course, for a quick check you could also write a query to loop through databases on an instance and use the multi-server execution feature in SSMS 10.)

So here’s the query I’m using to find the last full backup, complete with some pseudo-code explaining a bit about how it could be used…

Continue Reading →

Comments { 0 }

Backups – Using SQL Agent Tokens to Set the Date, and Why to go MultiFile

by on June 1, 2009

My life is a bit easier since I learned how to use SQL agent tokens. They are particularly nice for setting date and timestamps on backup files. Unfortunately they only work in the context of executing agent jobs.

We had some backups at work which were running quite slowly, and my colleague Gina reminded me of something I once learned, but had somehow forgotten: backups streamed to multiple files are much faster than those written to single files. This is true even when the files are sitting on the same drive.

We’ve switched over the large backups from the existing process, where they wrote a single file to local disk and then robocopied out to a remote location, to the new process where the backup is streamed directly across the network to the backup location using multiple files. The total job runtime is always lower, and in most cases the time running the backup step itself is slower. I am finding that even when the backup process time itself is slightly longer, I perfer this setup because we don’t need to execute batch files stored on the operating system (with its related security worries), nor do we have to worry about allowing memory space for the copy process to run in outside of sql server. (Along with everything else that uses up that memory space.)

Here is a sample of the modified backup commands we’re using— this one is from an 11GB SQL 2008 database where I’m using five files for the compressed backup. The total backup size across the file files is 5.5GB and it completes across the network (within a datacenter) in ~10 minutes.

BACKUP DATABASE MyDB
	TO DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_1of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_2of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_3of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_4of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_5of5.DMP'
WITH COMPRESSION
Comments { 0 }