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.
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);
}
}
}
}
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/
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:
Create a Windows Forms App
Create a DBML file (LINQ to SQL classes) called Foo
Drag Tables/Objects you're interested in to the design surface
Close / Save
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.
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.
It is possible to do what you ask programmically. These are the steps:
exec sp_msforeachtable 'truncate table ?'This creates a schematically identical database of your "source" database named "source_test". All the triggers, constraints, stored procedures, SQL Server accounts, and every other bit of data that isn't the data table contents or sequence numbers is preserved. All the sequence numbers will be reset to default value when their tables are truncated.
We use these 5 steps to clone our own SQL Server databases for testing and other uses. This works on SQL Server 2000 forward.
To backup the database, you execute the SQL statement in the form of:
"Backup database " + kstrDbName + " to disk = '" + strFullyPathedBackupFile + "' With Init";
"With Init" is a key option that tells SQL Server to overwrite the file specified if it exists. More information can be found by looking up the SQL Server documentation for "Backup database".
On SQL Server 2005 and newer, you can use the SMO to restore the database, and specify the database's new name, as well as renaming the database files (the mdf and the log file). Example code of how to do this can be found in the Microsoft SQL Server SMO documentation on the Restore functionality.
On SQL Server 2000, there is no SMO support, so it must be done manually, through executed SQL statements. That takes the form statement similar to:
string strSQL = "Restore database " + strDatabaseName + "from disk = '" + strSourcePathFile + "' with replace, " +
" Move '" + strDatabaseName + "_Data' to '" + strDestinationPathFile + ".MDF', " +
" Move '" + strDatabaseName + "_Log' to '" + strDestinationPathFile + ".LOG' "
Then you use an Alter statement to rename the actual MDF and LOG files to match the new name. Yes, this means on SQL Server 2000, you cannot have the source database and the destination database. Sorry. It might be possible to do the rename of the MDF and LOG file at the same time as the restore, but if so, my team couldn't get it to work. We've moved on to new SQL Server databases, but I thought I'd pull the old code for completeness for this answer.
The alter statement is in the form of:
string AlterDbSQL = " Alter database " + strDatabaseName + " MODIFY FILE(NAME = " + Path.GetFileNameWithoutExtension( strDestinationPathFile ) + "_DATA, NEWNAME = " + strDatabaseName + "_Data) " +
" Alter database " + strDatabaseName + " MODIFY FILE(NAME = " + Path.GetFileNameWithoutExtension( strDestinationPathFile ) + "_LOG, NEWNAME = " + strDatabaseName + "_LOG) "
Some notes: if all you are interested in is creating a test database, then you don't need to perform step 2 (connect to source) and step 3 (create backup) every time. Just create a "template" backup of your current database you want to test against by creating a backup, restoring it, and then truncating the tables. Back that database up. Now you have backup of a data content purged version of your database. It will be a smaller backup, so it will be faster to copy and faster to restore. All you need to do is add in your test data and it is ready for testing.
