Trying to restore a .bak in C#

18 Aug 2006 5:00 PM

Jayyde

Can I run a SQL command like:

RESTORE DATABASE NewDB FROM DISK =
'ApplicationStartupPath\SQLSE2005\DevDB.bak' WITH RECOVERY, MOVE
'DevDB_Data' TO 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\Data\\NewEB_Data.mdf', MOVE 'DevDB_Log' TO
'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Log.ldf'

while connected to the master db installed by default in SQLSE 2005? If not
how the heck do I run this command to restore this db for the client when
they install the app?

I'm trying to do this through VS 2003 so that would be another
question--2003 isn't going to have any problems doing commands for SQL 2005
is it? Or am I just screwed here?

TIA
Jayyde

18 Aug 2006 5:43 PM

David Browne

Show quote

"Jayyde" <a***@hotmail.com> wrote in message
news:%23e2w7euwGHA.4300@TK2MSFTNGP05.phx.gbl...
> Can I run a SQL command like:
>
> RESTORE DATABASE NewDB FROM DISK =
> 'ApplicationStartupPath\SQLSE2005\DevDB.bak' WITH RECOVERY, MOVE
> 'DevDB_Data' TO 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\Data\\NewEB_Data.mdf', MOVE 'DevDB_Log' TO
> 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Log.ldf'
>
> while connected to the master db installed by default in SQLSE 2005? If
> not
> how the heck do I run this command to restore this db for the client when
> they install the app?
>
> I'm trying to do this through VS 2003 so that would be another
> question--2003 isn't going to have any problems doing commands for SQL
> 2005 is it? Or am I just screwed here?
>

That should work fine, provided the account has permissions and the backup
and data files are all visible and accessable to the SQL Server. What
problem are you having?

David

18 Aug 2006 5:51 PM

Jayyde

As soon as the code below gets to RestoreCommand.ExecuteNonQuery() it throws
an exception.

****CODE*********************************************

strConnectionString = "Server=(local)\\SQLEXPRESS;";

strConnectionString += "Database=master;";

strConnectionString += "uid=sa;";

strConnectionString += "pwd=********;";

strRestoreSQL = "RESTORE DATABASE NewDB ";

strRestoreSQL += "FROM DISK = '" + Application.StartupPath +
"\\SQLSE2005\\OldDB.bak' ";

strRestoreSQL += "WITH RECOVERY, ";

strRestoreSQL += "MOVE 'OldDB_Data' TO 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Data.mdf', ";

strRestoreSQL += "MOVE 'OldDB_Log' TO 'C:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Log.ldf'";

RestoreConnection = new SqlConnection(strConnectionString);

RestoreConnection.Open();

RestoreCommand = new SqlCommand();

RestoreCommand.Connection = RestoreConnection;

RestoreCommand.CommandText = strRestoreSQL;

RestoreCommand.CommandTimeout = 0;

RestoreCommand.ExecuteNonQuery();

RestoreConnection.Close();

*******************************************************

- Jayyde

Show quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:eZx3X3uwGHA.4460@TK2MSFTNGP04.phx.gbl...
>
> "Jayyde" <a***@hotmail.com> wrote in message
> news:%23e2w7euwGHA.4300@TK2MSFTNGP05.phx.gbl...
>> Can I run a SQL command like:
>>
>> RESTORE DATABASE NewDB FROM DISK =
>> 'ApplicationStartupPath\SQLSE2005\DevDB.bak' WITH RECOVERY, MOVE
>> 'DevDB_Data' TO 'C:\\Program Files\\Microsoft SQL
>> Server\\MSSQL.1\\MSSQL\\Data\\NewEB_Data.mdf', MOVE 'DevDB_Log' TO
>> 'C:\\Program Files\\Microsoft SQL
>> Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Log.ldf'
>>
>> while connected to the master db installed by default in SQLSE 2005? If
>> not
>> how the heck do I run this command to restore this db for the client when
>> they install the app?
>>
>> I'm trying to do this through VS 2003 so that would be another
>> question--2003 isn't going to have any problems doing commands for SQL
>> 2005 is it? Or am I just screwed here?
>>
>
> That should work fine, provided the account has permissions and the backup
> and data files are all visible and accessable to the SQL Server. What
> problem are you having?
>
> David
>

18 Aug 2006 6:02 PM

David Browne

Show quote

"Jayyde" <a***@hotmail.com> wrote in message
news:eCktb7uwGHA.2208@TK2MSFTNGP05.phx.gbl...
> As soon as the code below gets to RestoreCommand.ExecuteNonQuery() it
> throws an exception.
>
> ****CODE*********************************************
>
> strConnectionString = "Server=(local)\\SQLEXPRESS;";
>
> strConnectionString += "Database=master;";
>
> strConnectionString += "uid=sa;";
>
> strConnectionString += "pwd=********;";
>
> strRestoreSQL = "RESTORE DATABASE NewDB ";
>
> strRestoreSQL += "FROM DISK = '" + Application.StartupPath +
> "\\SQLSE2005\\OldDB.bak' ";
>
> strRestoreSQL += "WITH RECOVERY, ";
>
> strRestoreSQL += "MOVE 'OldDB_Data' TO 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Data.mdf', ";
>
> strRestoreSQL += "MOVE 'OldDB_Log' TO 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Log.ldf'";
>
> RestoreConnection = new SqlConnection(strConnectionString);
>
> RestoreConnection.Open();
>
> RestoreCommand = new SqlCommand();
>
> RestoreCommand.Connection = RestoreConnection;
>
> RestoreCommand.CommandText = strRestoreSQL;
>
> RestoreCommand.CommandTimeout = 0;
>
> RestoreCommand.ExecuteNonQuery();
>
> RestoreConnection.Close();
>
> *******************************************************
>
>

Apart from having no local variables and too much string concatenation
there's nothing wrong with your code. When ExecuteNonQuery throws an
exception, there will be useful information in there indicating the cause of
the problem. What is it?

David

18 Aug 2006 6:26 PM

Tracy McKibben

Jayyde wrote:
Show quote

> As soon as the code below gets to RestoreCommand.ExecuteNonQuery() it throws
> an exception.
>
> ****CODE*********************************************
>
> strConnectionString = "Server=(local)\\SQLEXPRESS;";
>
> strConnectionString += "Database=master;";
>
> strConnectionString += "uid=sa;";
>
> strConnectionString += "pwd=********;";
>
> strRestoreSQL = "RESTORE DATABASE NewDB ";
>
> strRestoreSQL += "FROM DISK = '" + Application.StartupPath +
> "\\SQLSE2005\\OldDB.bak' ";
>

What is Application.StartupPath pointing to?

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

19 Aug 2006 12:25 AM

Greg Linwood

Hi Jayyde

Given that this script will be run during installation, I assume you want it
to actually CREATE the database, right? If so, you should include the
REPLACE option in the command, eg:

....
WITH REPLACE, RECOVERY, MOVE
.....

You should also qualify the path names to the .bak files properly. Your
installer should be able to give you the actual paths.

Regards,
Greg Linwood
SQL Server MVP

Show quote

"Jayyde" <a***@hotmail.com> wrote in message
news:%23e2w7euwGHA.4300@TK2MSFTNGP05.phx.gbl...
> Can I run a SQL command like:
>
> RESTORE DATABASE NewDB FROM DISK =
> 'ApplicationStartupPath\SQLSE2005\DevDB.bak' WITH RECOVERY, MOVE
> 'DevDB_Data' TO 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\Data\\NewEB_Data.mdf', MOVE 'DevDB_Log' TO
> 'C:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\Data\\NewDB_Log.ldf'
>
> while connected to the master db installed by default in SQLSE 2005? If
> not
> how the heck do I run this command to restore this db for the client when
> they install the app?
>
> I'm trying to do this through VS 2003 so that would be another
> question--2003 isn't going to have any problems doing commands for SQL
> 2005 is it? Or am I just screwed here?
>
> TIA
> Jayyde
>