I'm looking to create a new version of a database but with completely reloaded data - so I want to retain the database structure but none of the data.

Is there an easy way (read: without a DBA on staff) to copy an existing database structure - preferably with views and stored procedures - and not take the data along for the ride?

Thanks in advance for suggestions...!

What version of SQL Server are you using?

Do you have SQL Server Management Studio installed? (Or the Express version of it.)

You may be able to use the Copy Database Wizard. After you provide the version, we can give you more explicit directions.

Hi Arnie!

We're running SQL Server 2005.

We do have Management Studio installed - a flavor of version 9.

We did try using the Copy Database Wizard, but we halted the process when we saw that it looked like it was going to transfer the data to the new copy.

Honestly, even if there's a way to do a complete copy and then clean out all the tables (so that I still get my end result, which is a complete copy of the structure, stored procedures and views), I'll be happy as a clam.

Thanks!

My mistake Judith.

Using Object Explorer, [Right Click] on the database,

Select [Tasks],

Then [Generate Scripts...]

On the first screen, check the 'Script all objects' checkbox.

You can either save to a file or into a window. When the scripts are finished, go to the top and change the name of the database, then run the scripts.

That 'should' get you what you want.

the Scripts generated error when run, i don't think its checking dependencies when producing the scripts so its trying to make "stuff" that's dependent on other elements that haven't been created yet and for a large db manually correcting the scripts takes forever

it may be the options need tweaking but for complex databases i've never had much luck with the generate script tools in

In SSMS go to Tools -> Options -> SQL Serve Object Explorer -> Scripting page there is an option to 'Generate script for dependand objects'. Set its value to 'True' and try to generate the script. Otherwise you need to use third party scripting tolls like red-gate's SQL Compare or something like that...

- Krishnakumar S

Hi,

I tried using same GENERATING the script to export the database schema but was unsuccessful as some of the procedures were encrypted

Plz suggest how to export db scripts without data for encrypted objects

Thanks