Backup-Restore SQL server database using C#

[Hans]

Hi!

I'm trying to use SMO from C# to backup and restore (restore over an
existing database) a SQL-server database (SQL-server 2005). Everything is
working but when I add a few lines of code where I try to read the datafile
information which I will use to re-locate data files during the restore I
receive an error message.

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

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."}

The error message is not shown when the code above is executed but when I do
the actual restore (res.SqlRestore(svr). However if I comment the 4 lines
of code above everything works just fine.

I tried to set the master as initial catalog but with no success (same error
message). As soon as I touch FileGroups/Files the database seems to be
locked for some reason.

Version of SMO assembly
Runtime version: v2.0.50727
Version: 10.0.0.0

Regards
/Hans

P.s I tried the sqlserver.programming group but did not get any replies
there so I try here D.s

 

[Alberto Poblacion]

I'm trying to use SMO from C# to backup and restore (restore over an
existing database) a SQL-server database (SQL-server 2005). Everything is
working but when I add a few lines of code where I try to read the
datafile information which I will use to re-locate data files during the
restore I receive an error message.

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

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."}

The error message is not shown when the code above is executed but when I
do the actual restore (res.SqlRestore(svr). However if I comment the 4
lines of code above everything works just fine.

The problem is probably that, internally, SMO is doing a "USE
[database]" when you executing the previous lines to find the files and
groups. Therefore, Sql server thinks that the database is in use. You can
verify whether this is the case by means of Sql Server Profiler: run the
preceding lines step-by-step while you are running a trace in Profiler and
see what is being sent to the server.

I suggest not using SMO, and instead performing all the operations by
means of SQL Qeuries. The back-up and restore can be performed by sending
"BACKUP DATABASE..." and "RESTORE DATABASE..." queries. Listing the files
and filegroups can be achieved by means of Select queries against the
appropriate system views. When you do it in this way, you can open the
conection against the master database, and everything (except the backup)
can be done without accessing any other database, so you shouldn't have the
problems that you mention.

 

[Hans]

Thanks Alberto!

I managed to do it with SQL queries and actually I managed to do it with my
smo code also if I after my code

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


added dummy code

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

I guess SMO is doing what you said "use [database]" but it was not enough to
just set
db = svr.Databases["master"];
You have to access the FileGroups/Files collections before the "use master"
was executed and the lock on my database was removed.

I don't really have to go foreach either. Just fetching the name of the
first file will do as well.
db = svr.Databases["master"];
string dummy = db.FileGroups[0].Files[0].Name;

Have a nice day!

Regards
/Hans