Drop/Create Scripts

Question

Drop/Create Scripts

Aspurr

SSC Rookie

Points: 35
More actions
March 2, 2005 at 3:23 am

#88139

Hello,
I was wondering if there is any way of scheduling the creation of a drop/create script. I know how to run the 'Generate SQL Script' to create the drop/create script manually, but wanted to automate the task so new drop/create scripts are created monthly
Basically the data in the tables is not important in a disaster, but the quick creation of the databases are
Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 1

Why cann't you simply create a backup of your DB ? Then after restoring it you will have the DB structure and the data. Friendly speaking I donn't understand why do you need only the script of the DB structure.

Jonr SSCrazy Points: 2566 More actions · Answer 2

Aspurr,

Good point Vidas, but you might want to script the structure only, for source control reasons or to satisfy audit requirements. If you've got a 50Gb database it might not be an option to keep a copy every night of the week.

I'm not aware of any way to write a T-SQL script to do this (easily). However, if it's just a backup up the database structure that you need, you could use DTS. Right click on the database you want to script and run All Tasks -> Export Data..., select your source and destination databases. When you get to the dialog with the three radio buttons, select the bottom one (Copy objects and data between SQL Server databases).

Click on 'Next', then de-select the tick in the Copy data option box. You might also want to change the Default Options so that you get the data structures, stored procs etc, but not the users. Click 'Next', and click on 'Schedule DTS package for later execution' if you want to run the job on, say, a daily basis.

This doesn't give you a re-runnable script, but at least you can have a backup of the database structure on another server for DR purposes. As it contains no data, it can be easily back'd up and archived without consuming excessive disk space.

If anyone knows of a better method, or a way to do this in script which can dump the drop/create statements out to a file, I'd be grateful to hear it too

Jon

Aspurr SSC Rookie Points: 35 More actions · Answer 3

That is right jon, I want hold the resulting scripts in sourcesafe. The databases are very large and although we do backups I just wanted the schema including indexes, SP e.t.c so should we need to recreate in a development enviroment (or whereever) then we could just run the latest drop/create script

I will have a look at DTS

Thanks

Jonr SSCrazy Points: 2566 More actions · Answer 4

Pleasure, if I discover a better way, I'll post back here and let you know.

Jon

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

- take a look at SQLDMO

- maybe this can help you out ...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=107508

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q220/1/63.asp&NoWebContent=1

"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s yourserver /d yourdb /I /F filepath

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Aspurr SSC Rookie Points: 35 More actions · Answer 6

Thanks for the quick responses, I will let you know how I get on

Jonr SSCrazy Points: 2566 More actions · Answer 7

Very nice Alzdba, wasn't aware of SCPTXFR

Try this one too if you fancy doing some SQLDMO coding:

http://msdn.microsoft.com/msdnmag/issues/01/05/sqldmo/default.aspx

Jon

Kevin Anderson SSC Rookie Points: 45 More actions · Answer 8

I have a similar requirement but where SCPTXFR has the option of all objects in 1 file or grouped by file type, I need 1 object per file and prefer TSQL to SQLDMO. Is there an undocumented switch or any other suggestions?