Restoring database problem

[Mandy]

Hello All,

I tried to restore a msde database file in c#. Since SQL Server
requires that no users be connected to the database during the restore
operation, I tried to shut down the sql server, then start the sql
server again. Unfortunately, it did not work. The following is the
code:

//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}

Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");

//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@"..\..\backup\DBLPS.bak");

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");

// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);



An exception is thrown at srv.Start with message:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe

Additional information: [SQL-DMO]This server object is already
connected."



Mandy

 

[Frans Bouma [C# MVP]]

Hello All,

I tried to restore a msde database file in c#. Since SQL Server
requires that no users be connected to the database during the restore
operation, I tried to shut down the sql server, then start the sql
server again. Unfortunately, it did not work. The following is the
code:

//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}

Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");

//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@"..\..\backup\DBLPS.bak");

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");

// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);



An exception is thrown at srv.Start with message:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe

Additional information: [SQL-DMO]This server object is already
connected."

And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE) You
could even shutdown the mssqlserver process through executing "net stop
mssqlserver" as a process and then restart it again with "net start
mssqlserver" as a process.

FB

 

[Mandy]

Hi,

Thanks for replying! How to execute T-SQL commands to stop and start
database in C#?

Mandy

Hello All,

I tried to restore a msde database file in c#. Since SQL Server
requires that no users be connected to the database during the restore
operation, I tried to shut down the sql server, then start the sql
server again. Unfortunately, it did not work. The following is the
code:

//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}

Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");

//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@"..\..\backup\DBLPS.bak");

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");

// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);



An exception is thrown at srv.Start with message:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe

Additional information: [SQL-DMO]This server object is already
connected."

And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE) You
could even shutdown the mssqlserver process through executing "net stop
mssqlserver" as a process and then restart it again with "net start
mssqlserver" as a process.

FB

 

[Frans Bouma [C# MVP]]

Thanks for replying! How to execute T-SQL commands to stop and start
database in C#?

stop and start the server process. You can do that through:
// stopping
System.Diagnostics.Process.Start("CMD.exe", "/C net stop mssqlserver");

// starting
System.Diagnostics.Process.Start("CMD.exe", "/C net start mssqlserver");

Of course you have to wait some seconds between the two.

Frans.

Mandy

Hello All,

I tried to restore a msde database file in c#. Since SQL Server
requires that no users be connected to the database during the restore
operation, I tried to shut down the sql server, then start the sql
server again. Unfortunately, it did not work. The following is the
code:

//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}

Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");

//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@"..\..\backup\DBLPS.bak");

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");

// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);



An exception is thrown at srv.Start with message:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe

Additional information: [SQL-DMO]This server object is already
connected."

And if you use the T-SQL commands instead of SQLDMO? (RESTORE DATABASE)
You could even shutdown the mssqlserver process through executing "net
stop mssqlserver" as a process and then restart it again with "net start
mssqlserver" as a process.

FB