How can I programatically clone a database schema in SQL Server?

Question

I would like to set up some solid testing on my project and the way I'd like to do it is have a commandline program that I can run that will copy just the structure of a database and not the actual data. Then I can run my tests on that new database.

Ideas?

Update: someone said I should specify a language. I was thinking TSQL as that way if Sql Server runs, the script should run. Also, it's SQL Server 2005.

Answer 1

you can use the following approch http://fahadaz.blogspot.com/2008/10/database-scripts-and-contiguous.html but it required your database create scripts.

If you don't have those you can use the following article to generate them http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

Answer 2

You don't say which version of SQL Server you're using, if its 2005 look at DMO - Database Management Objects, a set of COM libraries that give you access to the functionality found in Enterprise Manager/Management Studio.

For 2008 we have SMO similar functionality but as .Net assemblies, MS have some good code examples. Their scripting example looks like waht you need :-

Lifted from their site we have

//Connect to the local, default instance of SQL Server. 
{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 

   //Define a Scripter object and set the required scripting options. 
   Scripter scrp = default(Scripter); 
   scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 

   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Table tb = default(Table); 
   Urn[] smoObjects = new Urn[2]; 
   foreach ( tb in db.Tables) { 
      smoObjects = new Urn[1]; 
      smoObjects(0) = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         StringCollection sc = default(StringCollection); 
         sc = scrp.Script(smoObjects); 
         string st = null; 
         foreach ( st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
}

Answer 3

The other answers work well if you want a thorough copy, but here is another approach that you may be intrigued by. If you are using LINQ to SQL, you can create a DBML file and then use the CreateDatabase() method on your data context. It does not copy the entire schema (omits some constraints, UDFs, Procs, etc...) but is useful in scenarios where you may want to operate on tables and perhaps partition your database testing.

Here are a few steps:

1. Create a Windows Forms App

2. Create a DBML file (LINQ to SQL classes) called Foo

3. Drag Tables/Objects you're interested in to the design surface

4. Close / Save

5. Some place in your application you could write code like:

   FooDataContext fooData = new FooDataContext(@"connection string to new database");

   fooData.CreateDatabase();

Here is the documentation for the method I described above.

Answer 4

Sadly, the most automatic process we found was actually using one of those mouse control tools to actually use SSMS to copy the database. I'd MUCH rather do something else, but none of the answers actually fit our requirements.