Post reply

Restore/Attach-Dettach

  • March 18, 2009 at 5:30 pm

    #201929

    I have 3 production servers in which each database is around 1 TB, we dont have any dev servers but we recedntly established a new server so that we can do all our monthly updates on the new server (Stage) then move them to the production server per dataabase.

    I am sure many of the experts here might have dealed this and so i would like to know the best process to do my monthly updates with less down downtime on the production.

    Here is my plan.

    1. Restore backup of my production on to stage and do all my updates and then restore it back to production - approx downtime for production may be 5hrs as we use litepspeed to restore.

    2. Restore backup onto stage and do all our updates then dettach mdf file from production, move to production on different drive other than existing production mdf file and attach to production then delete old mdf file from production.

    At this point am not sure how i can handle this but i do have 20 databases of around 1 TB to handle every month.

    Please suggest me the BEST option without using any third party tools, we do have SQL 2005 Enterprise and want make use of it for my requirement.

    Thanks

  • March 18, 2009 at 6:38 pm

    #962390

    If you use LiteSpeed, you may consider to use backup/restore tables instead of whole database.

  • March 19, 2009 at 1:14 pm

    #963055

    I didnt check that option on litespeed where is it?

    anyways hows does that help restoring all tables orrestoring database would be same for me.

  • March 20, 2009 at 1:20 pm

    #963744

    Check this-->http://www.quest.com/Quest_Product_Demos/LiteSpeedforSQLServer_demo_043007/chap02.htm

    MJ

  • March 29, 2009 at 8:16 am

    #968013

    I am trying to calculate restore time for all prod databases which range from 300gb to 1.4TB. Once i analyse this i want to implement backup restore for my production updates for which i have a dedicated stage server.

    HEre is the plan.. every time i want to update (estimate time 25hrs) a database, i will be restoring prod database onto stage do my updates then restore the copy from stage onto production.

    When did test restoring i am getting diffrent duration for a single database. for example i restored a database of 1.4TB exec command from query window in 9.30hrs and the same database when i resotred by sheduling a job in sql agent it took around 6hrs. there is a lot of diffrence in restoring a single database, is this the way i have to estimate? any ideas?

  • March 30, 2009 at 11:19 am

    #968529

    Any Ideas on how to calculate / estimate exact restore time of a database ?

  • March 30, 2009 at 11:51 am

    #968541

    I'm trying figure out what you are attempting to accomplish. Are you restoring a current copy of a database to stage, applying updates that need to go into production on the stage database, then moving the stage database out to production?

    If so, what about all the work done in production between the restore to stage, the updates, and then rolling it out again? Aren't you going to lose data?

    Just curious, because this is what I am reading in your posts.

  • March 30, 2009 at 12:11 pm

    #968550

    I was gonna ask same question as Lynn had.

    How are the PROD DBs being used?

    Just read-only?

  • March 30, 2009 at 12:59 pm

    #968585

    you are right but that is acceptable. we dont update anything directly in production, we only read from production. update will be made only once when we plan on stage then that will be moved to production.

    Actually my other question was how to estimate resotre time not related to updates.

    I did test one database restoring 2 times to the same server but the time it took was completely diffrent.

    Restore database " REVBud" to server A = 10.12 hrs

    drop database "REVBud"

    Restore database " REVBud" to server A (2nd time) = 8.20 hrs.

    I wonder why there is so much difference restoring same database,same size to same server .

  • March 30, 2009 at 1:04 pm

    #968590

    I just have to be sure here, your production databases are simply read-only databases except when there is an update. No data is being inserted, updated, or deleted on a regular, day to day basis.

  • March 30, 2009 at 1:31 pm

    #968615

    yes. Data will be updated by a job except that no one else will insert any kind of data.

  • March 30, 2009 at 1:31 pm

    #968616

    Think about logshipping?

    Set them up and only SHIP LOG once a month. the restore will be only restoring your updates, not the whole database, this will save a lot backup/restore troubles.. this will assume your staging server could host all those DBs tho.

Viewing 12 posts - 1 through 11 (of 11 total)

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