CODEWITHSUNDEEP
c#sql-server
Christian Tang
Christian Tang

Reputation: 1450

Problems with restoring SQL Server database

I have been working for hours now trying to figure out why I can't get my C# application to restore a SQL Server 2008 database. I use the following code:

var conn = new SqlConnection("SERVER=Server;DATABASE=master;User ID=sa;Pwd=Password");
var srvConn = new ServerConnection(conn);
var srv = new Server(srvConn);

Restore res = new Restore();
res.Database = "RestoredDatabase";
res.Action = RestoreActionType.Database;
res.Devices.AddDevice("Path to .bak-file", DeviceType.File);
res.ReplaceDatabase = false;
res.SqlRestore(srv);

When I run the code nothing happens. No errors or exceptions occurs! I have been looking around the web for a solution, but all examples are exactly the same, so no help there.

Could it be a problem with the connection, or maybe a problem with the .bak-file?

I'm surprised that there are no real debugging-features such as exceptions when using objects from Microsoft.SqlServer.Management.Smo.

Upvotes: 0

Views: 787

Answers (3)

tsells
tsells

Reputation: 2771

You can also roll your own code if you like. I use this in some integration tests to restore databases to a clean state. I reset the database to single user mode so if anyone is connected to it - it will kick them off so I can restore it. 

StringBuilder sb = new StringBuilder();
sb.Append("ALTER DATABASE ");
sb.Append(" [");
sb.Append(databasename);
sb.Append("] ");
sb.Append(" SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");
sb.Append("RESTORE DATABASE ");
sb.Append(" [");
sb.Append(databasename);
sb.Append("] ");
sb.Append(@" FROM DISK = N'D:\SQLBACKUPS\AUTOMATION\");
sb.Append(backupfilename);
sb.Append("' WITH FILE=1, NOUNLOAD, REPLACE, STATS=10");
return sb.ToString();

Then just create a sql command object and pass in the results from the string builder as the command text and execute.

Upvotes: 1

daniloquio
daniloquio

Reputation: 3912

  • First, try restoring the database using the management studio options, using the same authentication info and the same .BAK. If it don't works, the problem is not on your code.
  • If the first experiment worked fine, and supossing the database is already created, make sure no one is connected to it before launching restore. You can execute the instruction sp_who2 in Sql Server to make sure there is no connections to that database.

Upvotes: 1

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

This may be some permission Problems, due to which also sometimes the restore will not happen. Try taking restore of the ".bak" file first with your local databases which is accessible with your username and password. Any how visit the Sql BackupRestore site for more details about Sql Backup using C#.net. Hope it will help you.

Upvotes: 0

Related Questions