Not a new database, but an empty database – no data, but it still preserves the existing schema. You have choices:
1. Use a combination of Truncate/Delete
USE MyDB
EXEC sp_msforeachtable ‘print ‘’TRUNCATE TABLE ?’’’
The command will truncate all tables in the database, EXCEPT when a column in a table has a constraint such as a foreign key. This will complicate matters because SQL Server will throw an error: ’Cannot truncate due to foreign key constraint’ or something similar. Depending on how big is the table, I usually run a DELETE with no WHERE clause. It achieves the same result, although you should keep an eye on your log, making sure you still have some space to move around.
2. Clone the existing database
A better method of creating an empty database is by creating a clone for an existing database. You create a database creation scripts by right-clicking on the target database>Tasks>Generate Scripts. Follow the prompts. Don’t use the default value as they may not include options that you need. Please refer to the Microsoft article http://support.microsoft.com/kb/914288/en-us for more information.
