How to copy a DB, with Smo?  
Author Message
Demokratizator





PostPosted: Visual C# General, How to copy a DB, with Smo? Top

Good afternoon.
How, with
Microsoft.SqlServer.Management.Smo, to copy a database under other name (i.e. the DB - bd1 should be copied it in bd2. Business occurs on one server MS SQL 2005)
But to copy, so that transferred all procedures, triggers, ....
Thanks!



Visual C#14  
 
 
George Birbilis





PostPosted: Visual C# General, How to copy a DB, with Smo? Top

You should ask this at an SMO newsgroup or forum I suppose

 
 
boban.s





PostPosted: Visual C# General, How to copy a DB, with Smo? Top

Operation of copying database is in fact operaction of backup existing database and restore the backup with different name. Code for backuping existring database will be something like this:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

public static bool Backup(string filePath)
{
ServerConnection srvConn = new ServerConnection("Instance name");
srvConn.LoginSecure = true/false;

//if LoginSecure = false then you have to set Login and Password value, if not, then you dont need to set them
srvConn.Login = username;
srvConn.Password = password;

srvConn.ConnectionTimeout = ;

Server srvSql = new Server(srvConn);
Backup bkp = new Backup
();
bkp.Action =
BackupActionType
.Database;
bkp.Database =
"bd1"
;
BackupDeviceItem bkpDevice = new BackupDeviceItem(filePath, DeviceType
.File);
bkp.Devices.Add(bkpDevice);
bkp.SqlBackup(srvSql);

return true;
}

Now to restore the created backup under different name the code will be like:
public static bool Restore(string backupFilePath)
{
ServerConnection srvConn = new ServerConnection
("Instance name");
srvConn.LoginSecure = true/false;

//if LoginSecure = false then you have to set Login and Password value, if not, then you dont need to set them
srvConn.Login = username;
srvConn.Password = password;

srvConn.ConnectionTimeout = ;

Server srvSql = new Server(srvConn);

Restore rst = new Restore();
rst.Action =
RestoreActionType
.Database;
rst.Database =
"bd2"
;
BackupDeviceItem bkpDevice = new BackupDeviceItem(backupFilePath, DeviceType
.File);
rst.Devices.Add(bkpDevice);
rst.SqlRestore(srvSql);

return true;
}

Of course values used should be either parameters of these method or extracted from some application setting values, but i hope this is enough to show you the example of doing the job.



 
 
Demokratizator





PostPosted: Visual C# General, How to copy a DB, with Smo? Top

Thanks!
How to make it that tables empty were: i.e. to copy only structure, stored procedures and triggers


 
 
MihaiBejenariu





PostPosted: Visual C# General, How to copy a DB, with Smo? Top


How to make it that tables empty were: i.e. to copy only structure, stored procedures and triggers



E.g. for tables you have to right like this:

foreach table in bd1.Tables

bd2.ExecuteNonQuery(table.Script)

More details here:

http://www.sqlteam.com/item.asp ItemID=23185




 
 
boban.s





PostPosted: Visual C# General, How to copy a DB, with Smo? Top

You should execute TRUNCATE TABLE TableName command for every table that should be cleared.

 Why not explain the task a litle more, and maybe someone will give better (or completly different) way of doing the job.