April 17, 2012 at 1:23 pm
Hi All
First off, I am not an expert in the field of db so be gentle with me. However, I am not naive either so, you can throw tough questions.
I just started a new job at a company and found out that there is no DR for the production db. Off course, I freaked out and went on rampage to setup a DR for the db.
My background is Networking and System. In my previous job, I setup a DR with full backup every
night and a TLD every 30 mins from 8am -7pm. This files (linked to backup devices) were backed up every day. This way we can perform a recovery to the last 30 mins. Pls note that this was done with one database in production.
Now: the first thing with this new company is that there are over 20 databases on the production server and they are all being used (why? don't ask). This makes creating backup devices for each database big issue for me. also, since thay are all being used, the transaction log files are huge.
I set up LogShipping and it worked like a charm the first day. Since them, the restore has been failing. I believe this has to do with my retention period for the trans logs. Anyhow;
Question: (FYI: the db is on SQL 2005)
1. Should I use log shipping for this solution or create the devices for each db and perform the dump-copy-restore?
2. if log shipping is used, can the trans log created via log shipping be applied to the full db backup? The log shipping is btw 5am to 9pm and the full dump is at 9.30pm. So the question is, can the first trans logs at 5am be applied to 9.30pm full dump?
3. Should backing up the log shipping trans logs replace the trans logs DR from the devices on the production system?
Any insight or input with be highly appreciated.
- Flow
April 17, 2012 at 1:53 pm
Couple things...
Don't use backup devices. Backup to disk, give each backup its own file, preferably stamped with the datetime.
If you are running log shipping, you cannot have anything else backing up the log or the restores on the secondary will fail. Nothing to do with retention, the log backups taken as part of log shipping won't be deleted before they are applied, log shipping doesn't know anything about any other log backups taken.
Please read through this - Managing Transaction Logs[/url]
2. if log shipping is used, can the trans log created via log shipping be applied to the full db backup? The log shipping is btw 5am to 9pm and the full dump is at 9.30pm. So the question is, can the first trans logs at 5am be applied to 9.30pm full dump?
Providing nothing truncated the log between 21:30 and 05:00, yes. I would recommend however just run log backups all hours.
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
April 18, 2012 at 3:37 pm
Thanks Gila.
For the backup, should I have this setup as a SP or just via batch file? (commandline)
April 18, 2012 at 3:59 pm
Agent job or maintenance plan would be my preference. Maintenance plan is easy if you're fairly new.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply