May 5, 2008 at 12:17 pm
Is there any reason not using full backup + transaction log backup & restore for database migration? If I remember right, this method is not supported by Microsoft. I read an article last year somewhere, but could not find it anymore.
I am thinking if it works fine, that will reduce the down time for the migration. Anyone has related experience?
May 5, 2008 at 1:27 pm
I don't recall seeing that this is not supported by Microsoft. Be sure to run the Upgrade Advisor to make sure the database will be compatible with 2005.
These links should give you the information (and additional links) you are looking for:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2477578&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2534409&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2968260&SiteID=1
May 5, 2008 at 2:01 pm
To clarify, “Backup & Restore” method means doing a full backup and restore it to SQL2005.
What if an OLTP database (300 GB) with a very tight down time? Is it possible to do a full backup while the DB is still in use, and restore the full backup to SQL2005, then bring the database no user activity, do transaction log backup and restore the transaction log backup to SQL2005? This will shorten the down time.
It works in theory. Not sure why MS does not mention this way or does not support if I remember right.
May 5, 2008 at 2:23 pm
...do transaction log backup and restore the transaction log backup to SQL2005? This will shorten the down time.
I'm guessing this is where Microsoft may not support it. You can do a full database backup from 2000 and restore that database to 2005. What I'm not certain about is whether or not a log backup from 2000 could be restored to 2005. My guess is it can't, which is why Microsoft would not support it.
May 5, 2008 at 2:48 pm
"a log backup from 2000 could be restored to 2005"? Yes. I tested it. Not sure whether MS has anything to say about this method, or whether there is any hidden problem I am not aware of.
May 5, 2008 at 3:12 pm
I have done some successful migrations using full and differential backups. You will only need downtime the time it takes to do the differential backup and restore.
I think that it works with log backups too, but I haven't tried that.
Ola Hallengren
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply