Copy Sql Server Database Schema and Data using SQL Server Management Objects
by David Hayden ( Sarasota Florida Web Developer )
Filed: SQL Server Management Objects, SMO
Here is a bit of C# Code that I demostrated at the Orlando .NET Developer CodeCamp 2006. It shows you how to make a copy of a database ( Data and Schema ) in SQL Server using SQL Server Management Objects. There are a lot of options you can set. This justs accepts the defaults:
// Connect to Server
Server server = new Server(".");
// Get the Database to Transfer
Database db = server.Databases["Blog"];
// Setup transfer
// I want to copy all objects
// both Data and Schema
Transfer t = new Transfer(db);
t.CopyAllObjects = true;
t.DropDestinationObjectsFirst = true;
t.CopySchema = true;
t.CopyData = true;
t.DestinationServer = ".";
t.DestinationDatabase = "Blog2";
t.Options.IncludeIfNotExists = true;
// Transfer Schema and Data
t.TransferData();
// Kill It
server = null;
It is assumed that the destination database already exists on the server. If not, you will need to create it first with something like:
// Create Database Object
Database db = new Database(server, "Blog2");
// Create It in SQL Server
db.Create();
or set the property on the Transfer Object to create the target database:
t.CreateTargetDatabase = true;
Conclusion
You have to love SQL Server Management Objects ( SMO )!
Source: David Hayden ( Sarasota Florida Web Developer )
Filed: SQL Server Management Objects, SMO