Introduction
This article shows how to use SQL Server Management Objects (SMO) and C# to Script to copy database schema with or without data.
Using the code
I have created a class DBHelper
which is used to hold SQL Server instance information such as server name and database name, and the CopyDatabase
method is used to copy the database. Please make sure that you have SQL Server 2005 and MS SQL Server Management Objects installed on your computer.
public static void CopyDatabase(bool bCopyData)
{
Server server = new Server(DBHelper.SourceSQLServer);
Database database = server.Databases[DBHelper.SourceDatabase];
Transfer transfer = new Transfer(database);
transfer.CopyAllObjects = true;
transfer.DropDestinationObjectsFirst = true;
transfer.CopySchema = true;
transfer.CopyData = bCopyData;
transfer.DestinationServer = DBHelper.DestinationSQLServer;
transfer.CreateTargetDatabase = true;
Database ddatabase = new Database(server, DBHelper.DestinationDatabase);
ddatabase.Create();
transfer.DestinationDatabase = DBHelper.DestinationDatabase;
transfer.Options.IncludeIfNotExists = true;
transfer.TransferData();
server = null;
}
Points of Interest
Yes, we can play with SQL Server using Microsoft SQL Server Management Objects (SMO). Now I am in the process of writing a library which will interact with SQL Server for various parameters such as instance information, network information, backup and restore script, etc., and will publish it as soon as possible.