Post reply

I would like to have a snapshot staging database of live database

  • September 9, 2004 at 11:31 pm

    #85252

    Hi guru,

    I am a newbie for sql server.  What I would like to do is restoring our live database not using DTS(importing, exporting etc functions) but running a script(that is what I am looking for) in staging server. I need to get not only all data but also stored procedures and everything from our live database. Does any one have a script to do that?

    Highly appreciated in advance,

    Thanks 

  • September 13, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • September 16, 2004 at 5:00 am

    #523291

    Most SQL server scripts used to recreate a database don't include the actual data - unless the data set is very small. MySQL and similar database types DO include data, but it makes the scripts pretty big, and you'd have to keep them current.

    Ignoring the data itself, you create the script in Enterprise manager by highlighting a table, select All Tasks - Generate SQL script, and then click the various options to include -Script Database, Script users, stored procedures, triggers and tables (this isn't all check boxes but it should be obvious).

    You'll need to test the script, views or SPs which require a hierarchy can get scripted in the wrong order.

    If you need to make a copy of the whole thing with scripting, then you could script a database restore - but you'll need to move the file from A to B.  When you run your script, run it under 'master'.

    I don't know why you need to run the thing as a script specifically, but assuming that is not mandatory, other options for you are Transactional replication, or merge replication (merge is actually easier to set up but you shouldn't try it on a database which is still in development).  Merge replication is NOT as difficult as it sounds, it'll take you a day to learn how it works and it makes you look clever.

    Alternatively, one thing I do is to disconnect the source database, copy the log and data files over to the target box, then re-connecting them there. Saves irritating problems with getting the file locations right. 

    DTS packages ARE a pain in the *** when moving from A to B, as they can end up pointing in the wrong place. The best way around this particular problem is to have them point at Microsoft Data Files (create a blank notebook file, give it a  UDL extension then click it). Then you move the UDL file with the DTS and your connection problems go away.

  • September 17, 2004 at 3:59 am

    #523471

    The simplest solution is Log-Shipping. You could do this out of the box if you have enterprise edition of SQL Server, or using simple scripts from the resource kit if you have Standard or Personal editions.

    HTH,

    Paul Ibison

     (recommended sql server 2000 replication book:

     http://www.nwsu.com/0974973602p.html)


    Paul Ibison
    Paul.Ibison@replicationanswers.com

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

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