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.