I have a SQL 2008 DB. I am running a form that backs that DB up, then tries to update it. If the update fails the idea is to restore that backup. Here is the code I am using to restore the backup.
public void RestoreDatabase(String databaseName, String backUpFile, String serverName, String userName, String password)
{
Restore sqlRestore = new Restore();
BackupDeviceItem deviceItem = new BackupDeviceItem(backUpFile, DeviceType.File);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = databaseName;
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
sqlRestore.Action = RestoreActionType.Database;
string logFile = System.IO.Path.GetDirectoryName(backUpFile);
logFile = System.IO.Path.Combine(logFile, databaseName + "_Log.ldf");
string dataFile = System.IO.Path.GetDirectoryName(backUpFile);
dataFile = System.IO.Path.Combine(dataFile, databaseName + ".mdf");
Database db = sqlServer.Databases[databaseName];
RelocateFile rf = new RelocateFile(databaseName, dataFile);
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFile));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFile));
sqlRestore.SqlRestore(sqlServer);
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
}
The issue seems to be that the file names I pick are different from the online DB. I basically want to replace the database on the server with the backup. I get an exception when I call SqlRestore.
The main exception says
{"Restore failed for Server 'localhost'. "}
Digging into the inner exceptions shows these errors
An exception occurred while executing a Transact-SQL statement or batch.
and then
Logical file 'DB' is not part of database 'DB'. Use RESTORE FILELISTONLY to list the logical file names.\r\nRESTORE DATABASE is terminating abnormally.
I assume there is some way to tell this to just use replace the existing DB as is.
I use this bit of code to get the file path of the DB to have a directory to dump the backup. Maybe this could be used to get the file names to recreate.
public string GetDBFilePath(String databaseName, String userName, String password, String serverName)
{
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Database db = sqlServer.Databases[databaseName];
return sqlServer.Databases[databaseName].PrimaryFilePath;
}
I changed my back up and restore functions to look like this:
public void BackupDatabase(SqlConnectionStringBuilder csb, string destinationPath)
{
ServerConnection connection = new ServerConnection(csb.DataSource, csb.UserID, csb.Password);
Server sqlServer = new Server(connection);
Backup bkpDatabase = new Backup();
bkpDatabase.Action = BackupActionType.Database;
bkpDatabase.Database = csb.InitialCatalog;
BackupDeviceItem bkpDevice = new BackupDeviceItem(destinationPath, DeviceType.File);
bkpDatabase.Devices.Add(bkpDevice);
bkpDatabase.SqlBackup(sqlServer);
connection.Disconnect();
}
public void RestoreDatabase(String databaseName, String backUpFile, String serverName, String userName, String password)
{
ServerConnection connection = new ServerConnection(serverName, userName, password);
Server sqlServer = new Server(connection);
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database = databaseName;
BackupDeviceItem bkpDevice = new BackupDeviceItem(backUpFile, DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
rstDatabase.ReplaceDatabase = true;
rstDatabase.SqlRestore(sqlServer);
}
That way they just use whatever files are there. There are no longer and directives to relocate files.