December 1, 2009 at 9:21 am
Hello SQL Guru's,
I need an urgent help. We are migrating the sql 2000 to sql 2008. The final cutover b ackup strategy they are thinking to take the last full backup and restore on new server with no recovery and in week later take the last differential backup and restore with recovery. I am suggesting to just take a full consistent backup on final cutover and restore with recovery instead of full and diff. What do you say? If you are suggesting an option then please give the reason too. Thanks! The db's are about 100 to 200 GB size.
December 1, 2009 at 9:34 am
Sounds like you might want to consider log shipping or some flavor of it instead of differentials. That would give you the ability to look at your DB and see what's going on in a read-only fashion. It would also allow for a faster cut-over time.
The biggest problem I see with differentials is that the backups will be larger as you move further away from your last full backup. The log shipping option would be relatively quick and is not usually that hard to set up - it can even be set up on a non-Enterprise version through a little manual labor (I'm pretty sure there are scripts stored on this site to do the job.)
If your backups don't take that long, you may just want to be down for the time it would take to do the backup and restore. If you enable the "Instant File Initialization" permission, then the server won't create then 0 out the entire DB file. We needed to switch over pretty quickly so used log shipping. If you can afford to be down for an hour or two, I'd consider doing the full backup/restore as that would be easier.
As always, it depends on your scenario. 🙂
December 1, 2009 at 9:37 am
It really depends on how much down-time you are allowed,
if possible i would go for a full-backup and restore on the new server
December 1, 2009 at 10:02 am
We have time to have full backup and restore.The log shipping can not be done because there is no port open due to the IRS guidelines.
December 1, 2009 at 10:05 am
My main concern is if its possible to restore full backup with norecovery and week later restore the differential backup up. Is it going to be an issue to leave the db on no recovery for a week and then restore with the last diff.backup? Please reply-
December 1, 2009 at 10:06 am
I'm confused on that one. How were you planning to get the differential backups on to the target machine? Log Shipping works in pretty much the same manner - Restore full DB w/ no recovery. Backup Transaction logs from main server. Restore transaction log with no recovery on target server. As long as the servers can talk to the same file share, you should be able to ship logs.
If you have the time to do a full backup and restore, you may just want to do that for the easiest path unless you need to do something with the applied differentials.
December 1, 2009 at 10:09 am
It should not be a concern to leave the other database server running in No Recovery mode. We ran like that with pseudo log shipping for several weeks. The databases weren't as large at the time, but they weren't really small for the time, either. Maybe a total of 50-100GB total.
My question on that was whether you needed to apply differentials for some reason, but that being left aside, this should be workable.
December 1, 2009 at 10:15 am
The current backup plan we have is weekly full backup, daily 2 times differential backup, every 15 minutes log shipping. so the cutover schedule is right after the 2nd differential backup that's the reason they decided to have full backup restoration then on final cutover do the diff. restore with recovery. I like the logshipping idea as well. I suggested them yesterday and they said they cannot have the prod server port open and there is no shared networth location.
December 1, 2009 at 10:18 am
hydbadrose (12/1/2009)
The current backup plan we have is weekly full backup, daily 2 times differential backup, every 15 minutes log shipping. so the cutover schedule is right after the 2nd differential backup that's the reason they decided to have full backup restoration then on final cutover do the diff. restore with recovery. I like the logshipping idea as well. I suggested them yesterday and they said they cannot have the prod server port open and there is no shared network location.
With that in mind,you probably should consider a plan to ship the differentials over. With no shared network location, that sounds like someone will need to either open up an FTP port or attach a drive or something like that. As noted, there's no real issue that I can see with the plan other than not being able to do that automatically.
December 1, 2009 at 10:27 am
How do you ship the differential backup? Is it like log shipping? We already have a one logshipping on place on this server. This is sql 2000 and I almost forgot how it works. Can you schedule another logshipping with diff on same server?
December 1, 2009 at 10:34 am
Probably just not understanding your setup, but log shipping is basically:
1. Take a full DB Backup of your Source database
2. Restore that Full DB Backup with No Recovery to your target
3. Take Log Backups of Source
4. Apply those Log Backups to Target
#3 and #4 imply the need for a shared storage location accessible to both servers.
Restoring the differential should be similar, but now that I think about it a little more, I don't think you can restore multiple differentials with no recovery. I think you need to re-restore the original full backup and then restore the latest differential. That doesn't help you a whole lot in your scenario, though you could be restoring the last full backup at the same time as you're taking the final differential backup.
Still, without some way to get those backup files over to the target server, you're going to have some issues. That can be done by FTP, shared network location, physically attaching a storage device or even by swapping in hard drives. Without some plan in place there you'll have issues.
If you're already doing log shipping to a different server, you'll probably want to approach the log shipping method carefully. It would be very easy to get out of sync with those log backup files and once you miss one because it's deleted, you'll have to start all over with the process. The great thing about it is that you can do log shipping with a set of custom scripts as there's nothing really special about the process.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply