Bite my bytes

What I learn by day I blog at night - A blog from Microsoft Consultant working from Ljubljana, Slovenia

  Home :: Contact :: Syndication  
  999 Posts :: 7691 Comments :: 235 Trackbacks

Search

Most popular posts
in last 90 days

Categories

My Projects

Archives

Stuff


Copyright © by David Vidmar
 
Contact me!
 
LinkedIn Profile
 
 
 

Everyone know that you can restore a SQL Server database with a rather simple RESTORE DATABASE T-SQL statement. But soon after that you discover that your own connection will get in your way of the restore. And that clearing the pool and other soft ways of getting connection down is not very fun.

That's where SQL Management Objects come into play and restoring a database becomes a really nice piece of code. (framework 2.0 and SQL Sever 2005 required)

First, add some references:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum

And we are ready to code. First, let's connect to SQL server (not the database!).

Server server = new Server(serverName);

Then we will drop all open connections.
Database database = server.Databases[databaseName];

if (database != null)
    server.KillAllProcesses(databaseName);

And we are ready for restore!
Restore restore = new Restore();
restore.Devices.AddDevice(backupFilename, DeviceType.File);
restore.Database = databaseName;
restore.ReplaceDatabase = true;
restore.PercentCompleteNotification = 10;
restore.PercentComplete += new PercentCompleteEventHandler(restore_PercentComplete);
restore.Complete += new ServerMessageEventHandler(restore_Complete);

restore.SqlRestore(server);

This sample assumes that you are restoring the database on same server, so you don't have to change file paths. Check the docs for more detail.

Inspired by CodeProject article and blog post.

PS: This post is also a test for Insert Code plugin for Windows Live Writer.

Posted on Wednesday, May 09, 2007 10:53 PM | Filed under: Developement |
Comments have been closed on this topic.