Re-create an existing sql server database programatically?

Is it possible to either copy and existing database, or generate t开发者_运维技巧he sql and then run it against a sql database to create a copy of an existing database?

I don't care about the data, just the schema.

I want to do this for unit testing. I know I can just use sqlite, but I am curious how this would work anyhow.

I don't have any stored procs or anything, simply tables and PK/FK etc.

I guess the best approach would be somehow to generate the sql schema, then just run it against the database.

Or if it is easier, copy the database and then truncate the tables.

Note: I want to know how this can be done programatically.

---

You can generate scripts by right clicking on database -> tasks -> generate scripts from sql management studio. In wizard you can under advanced settings (depending on sql management tools version) choose whether you want schema, data, or both.

UPDATE

To create database programatically you can use generated sql and execute it via ADO.NET. Or, if you use CodeFirst, it can do this automatically if you set CreateDatabaseIfNotExists initializer for context in your test class. You can create CodeFirst context from your database by reverse engineering existing application using Entity Framework Power Tools for Visual Studio. In that case you only need to specify connection string to non-existing database and CF will create it for you. You can also specify DropCreateDatabaseAlways to have your database refreshed each time when you run your test (same as executing sql query every time after you prepend drop for database in question to generated sql script) If you use codefirst, then you do not need to generate sql scripts.

If you prefer any of these methods, I can provide you with more resources if you want?

---

You can do it programatically in nhibernate. Nhibernate creates all the tables and the relations in database you only create The Database instance (name) in any Database server like Oracle, MySQL, MsSQL...

You can look this for details.

---

You would use the objects in the SQL Server Management Objects Class library.

For instance, the Database class implements IScriptable. I'd use that as my starting point (but I haven't done this myself). It's possible that the script this generates might have too much that isn't relevant, in which case you might have to enumerate individual tables and generate scripts for them.

But SMO is always the answer (these days) to "how do I do management task X against SQL Server?".