Prev: Date Difference Between 2 Rows for Readmission Calculation
Next: Fail to attach DB in windows 7
From: Hans on 6 Apr 2010 04:04
Hi!

I'm using SMO from C# to restore a database from a backup file. Since I want
to re-use the same datafiles I try to read the datafiles in my destination
database and call RelocateFile to use these filenames during the restore.
Everyting works fine except when I try to read the current datafilenames
(see code below). If I skip this code and just hardcode the paths in
relocatefile everything works fine.

Database db = svr.Databases[destinationDBName];
foreach (FileGroup group in db.FileGroups)
foreach (DataFile file in group.Files)
Console.WriteLine(file.FileName);


The problem is not to read the filenames but when I later on do the actual
restore

res.SqlRestore(svr);

I receive the following error
{"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 tried to set the initialcatalog to master when I connect to the
database but I still get the same error message. As soon as I touch the
svr.Databases the database gets locked.

Regards
/Hans


From: Hans on 8 Apr 2010 09:07
For anyone interested it seems that when you access the FileGroups/Files
collections SMO is doing an implicit "use [database]" which locks that
database. I tried to solve this earlier by doing

Database db = svr.Databases[destinationDBName];
foreach (FileGroup group in db.FileGroups)
foreach (DataFile file in group.Files)
Console.WriteLine(file.FileName);
db = svr.Databases["master"] //Added this dummy row

but that was not enough. You also had to access the db.FileGroups collection
like

Database db = svr.Databases[destinationDBName];
foreach (FileGroup group in db.FileGroups)
foreach (DataFile file in group.Files)
Console.WriteLine(file.FileName);
db = svr.Databases["master"] //Added this dummy row
string dummy = db.FileGroups[0].Name;


After I have added the "string dummy = db.FileGroups[0].Name;" the lock on
my destionationDB was released.

Regards
/Hans


 | 
Pages: 1
Prev: Date Difference Between 2 Rows for Readmission Calculation
Next: Fail to attach DB in windows 7