How do I copy SQL Server 2008 database via C# code

Question

Hi! I want to copy an existing SQL Server 2008 database schema, but not the data, to a new database on the same server (i.e MyDB => MyDB_Test) using C#.

I found some sample code at http://www.codeproject.com/KB/cs/CopyDBSchemaUsingSMO.aspx that seems to do exactly what I want but it kept throwing the following error on the ddatabase.Create() line:

Could not load file or assembly 'Microsoft.SqlServer.BatchParser,
Version=9.0.242.0, Culture=neutral,

PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.":"Microsoft.SqlServer.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

I tried to find the missing DLL but I was only able to find a version for SQL Server 2005. I'm not sure if I should use that, look for an alternate download or take an entirely different approach.

Any help would be greatly appreciated!!

Answer 1

A few points that spring to mind, which I think are things to consider:

It sounds like this is not going to be a one-off, but something you want to do on more occassions, so I would suggest:

1. keeping the entire db creation scripts and running those. IMHO, this is a good thing to have anyway within source control
2. have a clean (empty) backup of the database (a template copy essentially), and restore that as a new DB

Answer 2

I've seen incomplete installations of SQL Server 2008 components on machines - in that case, just head on over to Microsoft's download site and grab yourself the Microsoft SQL Server 2008 Management Objects (just a bit below the middle) in the appropriate version (x86 32-bit, or x64 64-bit or IA64 64-bit - depending on your needs).

Install that and try again.

Something else you might want to consider is buying and using an existing tool, instead of writing all the code yourself. Check out e.g. Red-Gate's SQL Compare which allows you to do this - compare two databases (their structure) and make sure they're both on the same level.