Backup and restore on a daily basis

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

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

Reply | 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

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

Reply | 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

Thanks Jezza, lots of reading to go with there.

Reply | Quote