Stored Procedure to Duplicate A Database

  • jnuneznyc

    Grasshopper

    Points: 14

    Is there a stored procedure that will allow me to duplicate a database before I load data into it? For example... I have a LIVE_DB and BACKUP_DB via ASP I would like to DUPLICATE LIVE_DB into BACKUP_DB. I have already programmed the switch over to the BACKUP_DB. After the duplication and switch I need to load in the new data and repopulate the full-text indexes.

    Thanks for any help,

    JNUNEZNYC

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720491

    You would have to write something to move all objects and data. There is a DTS task that will do this, perhaps you could schedule the package and use sp_start_job to run it from ASP.

    Steve Jones

    steve@dkranch.net

  • nigelrivett

    SSCertifiable

    Points: 5362

    You mean you want a copy of the live database without data?

    You could create an empty live database by scripting the objects (or transfer via dts or dmo) then take a backup. The empty database could then be created by restoring the backup.

    You could also create a database and run the scripts via osql.

    If you want to transfer the objects via dmo here is a script that will do it - just change the objects transferrred and servers/databases/passwords.

    http://homepage.ntlworld.com/nigelrivett/SQL-DMOTransfer.html


    Cursors never.
    DTS - only when needed and never to control.

Viewing 3 posts - 1 through 3 (of 3 total)

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