Introduction
This article show how to use SQL Server Management Objects (SMO), C# to Script, Copy Database Schema with or without data .
Using the code
I have created a class DBHelper which is used to hold the SQL Server Instance Information such as Server Name, Database name and having CopyDatabase method used to copy the dastabase. 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 the SQL Server using Microsoft SQL Server Management Objects (SMO). Now I am in process of writing a library which will interact with SQL server for various parameters such as Instance information, Network Information, Backup, Restore and Script etc. and will publish the same as soon as possible