Thursday, November 1, 2007

Backing up Sql Server 2005 with SMO

We recently decided that we needed a low maintenance backup strategy for our OLTP databases. The thing with working in contracting/consulting is it seems that you're working on a new database every other week. One of the clients we're working on right now has over 16 databases that we work on semi-regularly.

What makes things worse is that it seems we end up creating another one every other couple of weeks talk about a maintenance headache!

Normally for database backups I would recommend a SQL Server Maintenance Plan, but we wanted to do more than what SQL Server would generate for us via an SSIS package. We also didn't want to be changing the package every week as we drop and create more databases.

We wanted to be able to do the following:
1) Backup up ALL non system databases (except those in an "exclusion list")
2) Zip up all backups that we create (because you can get GREAT compression on .bak files)
3) Delete old backups after a certain time interval, say after accumulating 10 backups delete the old.
4) Send out summary emails so that we know the jobs are still running.
5) Be able to change these settings w/out knowing any T-SQL, C#, etc...

Because of this semi tall order we actually ended up writing some C#. At first I couldn't help but groan, I felt we were overly complicating the issue and creating an unnecessary maintenance headache (who wants to maintain more code).

After I spent some time on the Code Project though I felt a little better. SMO (SQL Server Management Objects) provides a WONDERFUL API in terms of manipulating SQL Server. This rich interface provides everything you might ever want to do to your data store through the Management Studio.

Some of the code ended up looking a little like this:

Backup backup = new Backup();
backup.Database = database.Name;
backup.Action = BackupActionType.Database;
backup.Devices.Add(new BackupDeviceItem(backupPath, DeviceType.File));
backup.Initialize = true;
backup.Checksum = true;
backup.ContinueAfterError = false;
backup.Incremental = false;
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.PercentComplete += new PercentCompleteEventHandler(backup_PercentComplete);
backup.Complete += new ServerMessageEventHandler(backup_Complete);
backup.SqlBackup(server);

It's ridiculously simple. Combined with the popular Zip library SharpZipLib we were able to do pretty much everything we wanted to our databases. As of right now our deployed utility runs as a SqlAgent job and backs up all databases (not in an exclusion list) before zipping them up. It then deletes the Nth oldest backup so that we don't have to clean up (we get a say, a 6 week revolving set).

One last thing, so that we could backup new databases without having to add any grants or new logins we created a single login user DBBackupOperator and put it in the server role 'sysadmin'. I know what you're thinking..."Tyler that's bloody CRAZY!" especially if you're gonna log in from some 3rd party app.

We decided that to save ourselves from living too dangerously we would hash at runtime the password that we store in the app.config. That is, the password we store in configuration files is NOT the password used to connect to the data store. Instead we hash the crap out of it and then used that shared secret password to connect.

Well that's it, I strongly encourage you to check out SMO the next time you're playing with SQL Server 2005.

Best,
Tyler

3 comments:

Lore said...

Hi! I implemented your backup code, but my database is very big and the process is more lated then when I do in SQL Server Managment Studio, do you know how I can do faster??

Thank you so much!

Lorena

Tyler Holmes said...

Hey Lore,

The SMO API is supposed to be what the Management Studio itself calls to perform actions against the data store.

If you find your own backup is running slower when done with code than what you're experiencing through the Management Studio I would make sure they're both running in the same context.

For example are they both backing up a file to the same locations. Is one backing up to some lag ridden share on the network? Are both databases clear of users who could be running long running queries?

Ideally the API and the Management Studio should behave extremely similar.

Also consider looking at the backup options you set in the Management Studio compared to those you set in code.

Best,
Tyler

Anonymous said...

hi,

do u know about any method by which i can get the list of the users currently connected to sql server database.

thanks..