How to copy a DB, with T-SQL?  
Author Message
Demokratizator





PostPosted: Visual C# General, How to copy a DB, with T-SQL? 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#15  
 
 
George Birbilis





PostPosted: Visual C# General, How to copy a DB, with T-SQL? 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 T-SQL? 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 T-SQL? 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 T-SQL? 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 T-SQL? 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.