Post reply

Drop/Create Scripts

  • 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

     

  • March 2, 2005 at 4:30 am

    #544819

    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.

  • March 2, 2005 at 4:48 am

    #544821

    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

  • March 2, 2005 at 4:54 am

    #544823

    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

  • March 2, 2005 at 4:55 am

    #544824

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


    Jon

  • March 2, 2005 at 5:02 am

    #544825

    - take a look at SQLDMO

    - maybe this can help you out ...

    http://qa.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

  • March 2, 2005 at 5:04 am

    #544826

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

  • March 2, 2005 at 5:12 am

    #544828

    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

  • May 5, 2005 at 6:17 am

    #557024

    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?

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

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