Remove data from database 1

[bembden]

I manage an SQL database with an access front end. I need to create a duplicate copy of the production database without any of the data.

I have created the duplicate database, however I am at a loss as to how to remove the data.

Any help appreciated


Bernie

 

[Mich]

Run 'DELETE FROM <table name>'. If you have a lot of tables try 'DELETE FROM (select name from sys.tables)'.

BE CAREFUL!!

-If it ain't broke, break it and make it better.

 

[RiverGuy]

It's easier not the copy the database itself. You can generate a script which will create the structure of the database for you (in script format)--without the data. For example, if it is SQL Server 2005, from within Management Studio, right-click on your existing database, choose Tasks, then choose Generate Scripts. A wizard will walk you through selecting your objects to create scripts for. It will generate a script you can run in a query window to create your object in a new database.

 

[bembden]

For example, if it is SQL Server 2005, from within Management Studio, right-click on your existing database, choose Tasks, then choose Generate Scripts. A wizard will walk you through selecting your objects to create scripts for. It will generate a script you can run in a query window to create your object in a new database.



Thanks, did that but before I run the script, will it create a new database of overwrite the database that I used to generate the script?

Thanks

 

[RiverGuy]

It depends on how you created the script. You can select options to drop the existing object first and recreate--which is what you don't want.

The easiest way is to just create a brand new database, and open a query window against this database. Paste in your script to create these objects in the brand new database.

 

[bembden]

The easiest way is to just create a brand new database, and open a query window against this database. Paste in your script to create these objects in the brand new database




Having some problems with users not found and constraints that do not exist

Will create the script again and let you know what happens

Thanks again

 

[RiverGuy]

Yeah, there are some options in the script wizard which are turned off that you can explicitly turn on.

 

[Mich]

Right-click your db in SSMS, select Script database as, CREATE TO, and put the script where ever you want it. Run the script making sure to change the db name. Then generate a script as RiverGuy suggests, open a query window within the new db, and run this script.

This will create a copy of your db without data, but only at the database level. You're going to run into problems with objects outside your database such as logins, maintenance plans, replication, etc. You may not need all of this. You can use SSIS to copy your logins, but I think the rest will have to be recreated.

-If it ain't broke, break it and make it better.

 

[bembden]

Thanks RiverGuy (and Mitch)


Finally got it to work

Bernie