Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Windows SQL server 2003.
    Currently my database is backed up fully each week and transaction every 12 hours.
    As an alternative DB to the LIVE system I've been asked to create a copy of the db to allow non-processing users to just view and report on data.
    As the application doesn't have enough rigidty to be able to tie users down to non-process items it seems sense to not have these users prodding and poking around in the live data.

    db is now created and works fine, but I'd like to automate the restore so as to have the copy db no more than 24hours out from live system without me doing a restore each morning.
    The first obstacle I see is that the back up copies are obviously named according to the date/time of the backup, so would restore recognise and pick up the latest copy?
    Would I have to automate the backup outside of the windows server management in order to specifically define the backup file name and then have restore use that same filename each time?

    Appreciate it if someone could point in right direction please.
    Thanks
    Alan

    Sunny Cheshire.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Alan

    Could you write a simple DTS package from Live to your Spare? I am not sure of the complexity of the database schema but you could do an UPDATE statement to update all records in the Spare with records from Live. You will have to identify the constraints , break and recreate them when doing a bulk copy, I do thison a regular basis when I update certain tables in my hot spare used for reporting hence taking a resource drain from my Live system.

    Not the solution but a method you could investigate.
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='jezza' post='794696' date='24-Sep-2009 12:25']Hi Alan

    Could you write a simple DTS package from Live to your Spare? I am not sure of the complexity of the database schema but you could do an UPDATE statement to update all records in the Spare with records from Live. You will have to identify the constraints , break and recreate them when doing a bulk copy, I do thison a regular basis when I update certain tables in my hot spare used for reporting hence taking a resource drain from my Live system.

    Not the solution but a method you could investigate.[/quote]

    Thanks Jezza but it's quite a complex db so I'm afraid it's out of the range of update procedure.
    Was thinking more of separate procedure within the sql server.
    Cheers
    Alan

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='ase001' post='794698' date='24-Sep-2009 12:52']Thanks Jezza but it's quite a complex db so I'm afraid it's out of the range of update procedure.
    Was thinking more of separate procedure within the sql server.
    Cheers
    Alan[/quote]

    OK, I manage a large CRM system with over 200 tables not including system tables and use this method quite successfully.

    You can use the RESTORE method in your maintenance plans, have a look here

    Restore database for starters
    Jerry

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='jezza' post='794783' date='24-Sep-2009 22:45']OK, I manage a large CRM system with over 200 tables not including system tables and use this method quite successfully.

    You can use the RESTORE method in your maintenance plans, have a look here

    Restore database for starters[/quote]

    Thanks Jezza, lots of reading to go with there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •