October 21, 2013 at 6:23 am
Greetings All,
I've assumed ownership of a backup\restore operation I'd to get an opinion on. My predecessor setup custom scripts for a daily restore for a handful of databases to a 2nd server for internal use (reporting, querying, etc...). The databases are restored each night and are restored to a fully operational mode.
I think I want to modify this and either:
1. Restore the databases as read-only, since they are only supposed to be used for reporting but nothing prevents some users from creating and modifying objects. These databases are also being caught up in the maintenance jobs, why reindex and dbcc when the database is going to be overwritten daily (and maintenance is performed on the 1st server as well)?
2. Use trans log shipping and leave the databases in a read-only\stand-by mode, then apply the logs during off hours and save the disk IO of restoring GB's of data every day when maybe only a few MB's of data has changed.
I guess I think either way I want to have the databases in a read-only mode to save the maintenance and lock management resources.
Thoughts?
October 21, 2013 at 6:39 am
Couple comments.
The process as it is now tests your backups. If you change to log shipping, then you lose that testing of your backups and you will need to either implement backup testing somewhere else or increase your risks.
Running CheckDB on the restored database means you don't need to run it on the source database. Remove that or switch to log shipping and you'll have to run checkDB on the main database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2013 at 11:58 am
Hi sqlpadawan_1, 😀
For reporting purposes, i would recommend you to use log shipping if you need up to date data in your read-lony database. If having data from D-1 is not a problem, leave it as it is, with the gain of testing your database backup and verying integrity.
Hope it helps. 😎
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
October 23, 2013 at 2:23 pm
Thanks all for the input. I'm going to plan on leaving the backup\restore process in place and schedule the dbcc's on the second server and not schedule them on the primary server for the databases being restored.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply