Prev: Number of subordinates using NESTED SET
Next: LongText not suppoeted as output parameter
From: Hans on 31 Mar 2010 05:26
Hi!

I have taken a database backup and want to restore that over an existing
database using SMO (C#).

The backup contains the datafile names of the backed up database but I want
to use the datafiles defined in the destination database (just overwrite the
data). I thought I could use the relocate files for this but it does not
work for me (I don't get any errors but the database is restored with the
same filenames as the source database).




SqlConnectionStringBuilder bu = new SqlConnectionStringBuilder();
bu.DataSource = serverName;
bu.IntegratedSecurity = true;

SqlConnection sqlCon = new SqlConnection(bu.ToString());
ServerConnection connection = new ServerConnection(sqlCon);
Server svr = new Server(connection);

Restore res = new Restore();

res.Devices.AddDevice(backupName, DeviceType.File); //add filename to
backup

res.Database = destinationDBName;
res.Action = RestoreActionType.Database;
res.ReplaceDatabase = true;
res.Restart = false;

res.RelocateFiles.Add(new RelocateFile("MYTEST", "C:\\Program
Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\MYTEST.mdf"));
res.RelocateFiles.Add(new RelocateFile("MYTEST_log", "C:\\Program
Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\MYTEST_log.ldf"));

res.PercentCompleteNotification = 10;
res.PercentComplete += new PercentCompleteEventHandler(res_PercentComplete);
_restoreEvent = restoreEvent;
res.SqlRestore(svr);

Regards
/Hans


From: Hans on 31 Mar 2010 08:04
Hi!

I found that I typed the incorrect logical name of the file (I typed the
logical name from the database I will overwrite but it should be the logical
name in the backup file).

Is it possible to also set the logical file name? What I want is to take a
backup of one database and restore that over an existing database and reuse
both the logical name and filenames but of course fetch the data from the
backup file). I konw this is possible using T-sql but I'm using SMO from C#
here.

Another thing is that during the restore I try to read the database file
information (i.e what filenames were used by the database before the restore
so I can call relocate files so I use the same filenames)

Database db = svr.Databases[destinationDBName];
foreach (FileGroup group in db.FileGroups)
foreach (DataFile file in group.Files)
{
Console.WriteLine(file.FileName);
res.RelocateFiles.Add(new RelocateFile("MYTEST", file.FileName));
}

The problem is that as soon as I touch the svr.Database object there is a
lock on the database and the restore fails with a

{"System.Data.SqlClient.SqlError: RESTORE cannot process database 'MYTESTDB'
because it is in use by this session. It is recommended that the master
database be used when performing this operation."}

I have read that I should set the
svr.ConnectionContext.DatabaseName = "master";

but that can't be changed so I tried to set that before I open the
connection but that did no help either.

Regards
/Hans


 | 
Pages: 1
Prev: Number of subordinates using NESTED SET
Next: LongText not suppoeted as output parameter