RESTORE DATABASE Error

[Mike]

I'm attempting to to use ADO.Net to execute a RESTORE DATABASE. Here
is a snippet of the code I am using:

SqlConnection connection = new SqlConnection();
string connectionString =
ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand count = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();

try
{
connection.ConnectionString = connectionString;
connection.Open();

count.CommandText = string.Concat("select count(*) from sysdatabases
where [name] = '", destinationDatabaseName, "'");
count.Connection = connection;
if((int) count.ExecuteScalar() > 0)
{
Console.WriteLine(string.Concat("Database ", destinationDatabaseName
," already exists."));
Console.WriteLine("Operation cannot continue. Please press
ENTER.");
Console.ReadLine();
System.Environment.Exit(0);
}

Console.WriteLine("Getting Logical Names and Physical Paths from
server...");
string GetFileInfo = string.Concat("RESTORE FILELISTONLY FROM disk =
'",sourceDatabaseName,".mdf'");

da = new SqlDataAdapter(GetFileInfo, connection);
DataTable dtFileInfo = new DataTable();
da.Fill(dtFileInfo);

Console.WriteLine("Performing RESTORE. This will take several
minutes...");
string CopyCommand = string.Concat("RESTORE DATABASE ",
destinationDatabaseName);
CopyCommand = string.Concat(CopyCommand, " FROM disk = '",
ConfigurationSettings.AppSettings["BackupFilePath"],
sourceDatabaseName, ".mdf' ");
CopyCommand = string.Concat(CopyCommand, " WITH RECOVERY, ");

for(int i = 0; i < dtFileInfo.Rows.Count;i++)
{
CopyCommand = string.Concat(CopyCommand, " MOVE '",
dtFileInfo.Rows["LogicalName"].ToString(),"' TO '",
dtFileInfo.Rows["PhysicalName"].ToString().Replace(sourceDatabaseName,destinationDatabaseName),
"' ");
if(i < (dtFileInfo.Rows.Count - 1))
CopyCommand = string.Concat(CopyCommand, ", ");
}

Console.WriteLine(CopyCommand);
Console.WriteLine();
Console.WriteLine(System.Environment.UserName);
Console.WriteLine("Please wait...");

cmd.Connection = connection;
cmd.CommandText = CopyCommand;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery(); //Blows up here!!!

The last line returns the following error:
SqlEx.Message =
"A severe error occurred on the current command. The results, if any,
should be discarded.\r\nProcessed 6528 pages for database
'SPRatebookTest', file 'SPRatebook0304_Data' on file 1.\r\nProcessed
150342 pages for database 'SPRatebookTest', file 'SPRatebook0304_Log'
on file 1."

SqlEx.StackTrace
" at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at
DataBaseCopier.DataBaseCopier.CopyDataBase() in
c:\\dotnetcode\\databasecopier\\databasecopier\\databasecopier.cs:line
213"

When I go to EM and check, the new DB is there and seems to be intact.

I take the CopyCommand string that VS.Net has and I copy-paste it into
QA and it executes fine. Can anyone give me any ideas on why this is
happening?!

TIA,
Mike

 

[Mike]

Thanks for the quick replies! I'm sorry, but I should have given some
background on what it is I'm trying to accomplish here. I am using EM
and QA simply to check my work. I am writing a console application
that will take a couple args and "process" the database. By process I
mean that the RESTORE with a new name is only the first step in many,
which I wish to automate.

I'm attempting to to use ADO.Net to execute a RESTORE DATABASE. Here
is a snippet of the code I am using:

SqlConnection connection = new SqlConnection();
string connectionString =
ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand count = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();

try
{
connection.ConnectionString = connectionString;
connection.Open();

count.CommandText = string.Concat("select count(*) from sysdatabases
where [name] = '", destinationDatabaseName, "'");
count.Connection = connection;
if((int) count.ExecuteScalar() > 0)
{
Console.WriteLine(string.Concat("Database ", destinationDatabaseName
," already exists."));
Console.WriteLine("Operation cannot continue. Please press
ENTER.");
Console.ReadLine();
System.Environment.Exit(0);
}

Console.WriteLine("Getting Logical Names and Physical Paths from
server...");
string GetFileInfo = string.Concat("RESTORE FILELISTONLY FROM disk =
'",sourceDatabaseName,".mdf'");

da = new SqlDataAdapter(GetFileInfo, connection);
DataTable dtFileInfo = new DataTable();
da.Fill(dtFileInfo);

Console.WriteLine("Performing RESTORE. This will take several
minutes...");
string CopyCommand = string.Concat("RESTORE DATABASE ",
destinationDatabaseName);
CopyCommand = string.Concat(CopyCommand, " FROM disk = '",
ConfigurationSettings.AppSettings["BackupFilePath"],
sourceDatabaseName, ".mdf' ");
CopyCommand = string.Concat(CopyCommand, " WITH RECOVERY, ");

for(int i = 0; i < dtFileInfo.Rows.Count;i++)
{
CopyCommand = string.Concat(CopyCommand, " MOVE '",
dtFileInfo.Rows["LogicalName"].ToString(),"' TO '",
dtFileInfo.Rows["PhysicalName"].ToString().Replace(sourceDatabaseName,destinationDatabaseName),
"' ");
if(i < (dtFileInfo.Rows.Count - 1))
CopyCommand = string.Concat(CopyCommand, ", ");
}

Console.WriteLine(CopyCommand);
Console.WriteLine();
Console.WriteLine(System.Environment.UserName);
Console.WriteLine("Please wait...");

cmd.Connection = connection;
cmd.CommandText = CopyCommand;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery(); //Blows up here!!!

The last line returns the following error:
SqlEx.Message =
"A severe error occurred on the current command. The results, if any,
should be discarded.\r\nProcessed 6528 pages for database
'SPRatebookTest', file 'SPRatebook0304_Data' on file 1.\r\nProcessed
150342 pages for database 'SPRatebookTest', file 'SPRatebook0304_Log'
on file 1."

SqlEx.StackTrace
" at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at
DataBaseCopier.DataBaseCopier.CopyDataBase() in
c:\\dotnetcode\\databasecopier\\databasecopier\\databasecopier.cs:line
213"

When I go to EM and check, the new DB is there and seems to be intact.

I take the CopyCommand string that VS.Net has and I copy-paste it into
QA and it executes fine. Can anyone give me any ideas on why this is
happening?!

TIA,
Mike