Restoring database problem

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."
MandyMandy wrote:
> 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
--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP|||Hi,
Thanks for replying! How to execute T-SQL commands to stop and start
database in C#?
Mandy
"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@.xs4all.nl> wrote in message news:<xn0dnlsxj3398s001@.msnews.microsoft.com>...
> Mandy wrote:
> > 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 wrote:
> 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
>
> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@.xs4all.nl> wrote in message
> news:<xn0dnlsxj3398s001@.msnews.microsoft.com>...
> > Mandy wrote:
> >
> > > 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
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP