Copy databases using SQL Server SMO

The following steps are required to perform the Copying a Database using SQL Server objects exposed to the .Net Framework by Microsoft. Net

1. Add reference to the project with the following DLL files,

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

Microsoft.SqlServer.SmoExtended.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll

These dlls are found in the following folder

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

2. Define a source Db and then the target DB, the source db will be scanned and then its contents [structure & data]

string sourceDB = "cs_notification_test";

string targetDB = "cs_notification_test1";

SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationConnectionString"].ConnectionString);

ServerConnection conn = new ServerConnection(connStringBuilder.DataSource, connStringBuilder.UserID, connStringBuilder.Password);

Server server = new Server(conn);

Database oldDb = server.Databases[sourceDB];

Database newdb = new Database(server, targetDB); newdb.Create();

Transfer transfer = new Transfer(server.Databases[sourceDB]);

transfer.CopyAllObjects = true;

transfer.CopyAllUsers = true;

transfer.Options.WithDependencies = true;

transfer.DestinationDatabase = newdb.Name;

transfer.DestinationServer = server.Name;

transfer.DestinationLoginSecure = true;

transfer.CopySchema = true;

transfer.CopyData = true;

transfer.Options.ContinueScriptingOnError = true;

transfer.TransferData();

3. Now, execute this code to create a copy from the existing DB.