Have you ever needed to create a copy of your database for development or testing purposes? Or wondered how the Database Copy Wizard works behind the scenes? In this article, we will explore how you can use SQL Server Management Objects (SMO) to programmatically transfer database objects and data from one server or database to another.

Setting up the Environment

Before we dive into the code, we need to set up our environment. We will be using C# and Visual Studio to write our code. Make sure you have the necessary assemblies referenced in your project. For SQL Server 2005, the assemblies are located in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. For SQL Server 2008, the assemblies are located in the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.

Next, we need to create two instances of the Server class, one for the source server and one for the destination server. You can connect to these servers using either Windows authentication or SQL Server authentication. Once connected, we can specify the source and destination databases.

Server mySourceServer = new Server(@"ARSHADALI-PC\ARSHADALI");
Server myDestinationServer = new Server(@"ARSHADALI-LAP\ARSHADALI");

// Using Windows authentication
mySourceServer.ConnectionContext.LoginSecure = true;
myDestinationServer.ConnectionContext.LoginSecure = true;

// Specify the source and destination databases
Database dbSource = mySourceServer.Databases["AdventureWorks"];
Database dbDestination = new Database(myDestinationServer, "AdventureWorksNew");

Transferring Database Objects and Data

Now that we have set up our environment, we can start transferring the database objects and data. We will be using the Transfer class from SMO to accomplish this. By default, the Transfer class will copy all objects, schemas, user-defined data types, tables, and stored procedures from the source database to the destination database.

Transfer transfer = new Transfer(dbSource);
transfer.CopyAllObjects = true;
transfer.CopyAllSchemas = true;
transfer.CopyAllUserDefinedDataTypes = true;
transfer.CopyAllTables = true;
transfer.CopyData = true;
transfer.CopyAllStoredProcedures = true;
transfer.DestinationServer = myDestinationServer.Name;
transfer.DestinationDatabase = dbDestination.Name;

transfer.TransferData();

By calling the TransferData method, the Transfer class will transfer the schema objects and data from the source database to the destination database.

Generating Scripts for Database Objects and Data

Sometimes, instead of immediately transferring the objects and data to the destination database, you may need to generate scripts for later execution. This can be useful if you want to create objects and push data on another server or database. With SMO, you can easily generate scripts using the ScriptingOptions class and the EnumScriptTransfer method.

ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptData = true;
scriptOptions.FileName = @"D:\TransferDatabaseSchemaAndData.sql";

transfer.Options = scriptOptions;
IEnumerable<string> scripts = transfer.EnumScriptTransfer();

foreach (string script in scripts)
{
    Console.WriteLine(script);
}

By setting the ScriptData property to true and specifying a file name, the EnumScriptTransfer method will generate scripts for the database objects and data. You can loop through the scripts collection to get each individual script.

Conclusion

In this article, we have explored how to use SMO to transfer database objects and data in SQL Server. We have seen how to set up the environment, transfer the objects and data, and generate scripts for later execution. SMO provides a powerful and flexible way to programmatically manage SQL Server instances and automate database tasks.