MS SQL 2008 - Create a copy of the database without the data

Question

In MS SQL Server 2008 R2, how do i create a new database based on the schema of the old one, but without copying any of the data along with it? I am using SQL Server management studio.

Sorry if this is a duplicate. I looked around and wasn't finding what i wanted. I assume this is pretty simple to do.

Answer 1

There is a nice script wizard that can help you. You can try it with Right Click on the database from the object explorer -> Tasks -> Generate Scripts. You can choose which objects from the database you want to export.

Answer 2

In SQL Server Management Studio, you can menu-click on an object and select...

"Script [Object] As" ... "CREATE to"... "New Query Window"

You would need to create scripts for each object you want to create.

If you want to do the whole lot, menu-click on the database, and select

"Tasks" ... "Generate Scripts" and follow the wizard, as per this article:

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 3

Right click the Database and select Tasks -> Generate Scripts.

You can you then select all the objects you require or only certain objects.

There are some Script Options that you should look at:

• Script USE DATABASE - You should set this to false if you are creating a new database with a different name.
• Indexes & Triggers are not scripted as default
• Logins & Object Level Permissions are not scripted as default
• Generate Script for Dependent objects - this is set to false as default but you may want to change it to true to ensure that all objects are scripted.

You can either create these to a new Query Window or save them to SQL Files.