InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:54:58 GMT20http://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]Robert Davis (6/18/2009)[/b][hr]I would like to say that the part below is not correct. A tail log backup is not a normal backup. to perform a tail log backup, you have to use the NoRecovery option on the backup log command. And when you do a tail log backup, it leaves the database in a restoring state so the database is not online any more. You don't have to take it offline.[quote]Perform a final Transaction Log backup on the old server (this is called the tail log backup), and then take the database offline if it is a shared server[/quote][/quote]Robert,Thanks, I learned something new from this. Fundamentally they are doing the same thing if the application is not running and accessing the database, but you are correct that the more appropriate way of doing it would be to use the NORECOVERY option of BACKUP LOG (I didn't know this was there until I read your response and I'll have to go play with it in a bit).Mon, 22 Jun 2009 09:49:28 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxnicely presented article, it is one of the best article on backup and restore topic which explained step by step process. thanksMon, 22 Jun 2009 09:37:40 GMThoney_tnrhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxGreat approach. Thank you for the article!Sat, 20 Jun 2009 15:10:28 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]Jonathan Kehayias (6/18/2009)[/b][hr]Perhaps you could point out the typo that you believe exists. This was written in Microsoft Word, and it passes a spell check, and manually reviewing it, there are no typos that I can find.[/quote]I didn't notice any when I read it the first time, but I read it again after seeing the above posts, and there are several typos, but on the positive side, all of the typos are spelled correctly.top instead of to, to instead of too, its instead of it's. Nothing major, and I wouldn't have noticed if it hadn't been pointed out.I would like to say that the part below is not correct. A tail log backup is not a normal backup. to perform a tail log backup, you have to use the NoRecovery option on the backup log command. And when you do a tail log backup, it leaves the database in a restoring state so the database is not online any more. You don't have to take it offline.[quote]Perform a final Transaction Log backup on the old server (this is called the tail log backup), and then take the database offline if it is a shared server[/quote]Thu, 18 Jun 2009 23:24:31 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]noeld (6/18/2009)[/b][hr]Ermm... Isn't this just a description of LogShipping ?[/quote]Yep, its basically manual log shipping. I've never used log shipping to accomplish this task because databases in my environment that I would be moving like this are already in FULL recovery. I generally off my existing backup set to perform all but the tail log restores. However, I have found in explaining this on the forums that people asking how to minimize down times, aren't in FULL recovery, and it is easier to explain how to manually do this, than to get them to setup log shipping to accomplish the same thing.Thu, 18 Jun 2009 21:17:39 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]daeguboog (6/18/2009)[/b][hr]Good article, very informative. You might want to spell check your article the next time you submit one. Justifiably or not, it can hurt your credibility if you submit an article with multiple spelling errors and typos to a professional forum. Just takes a second to run it through a spell checker.[/quote]Perhaps you could point out the typo that you believe exists. This was written in Microsoft Word, and it passes a spell check, and manually reviewing it, there are no typos that I can find.Thu, 18 Jun 2009 21:04:16 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxGood article, very informative. You might want to spell check your article the next time you submit one. Justifiably or not, it can hurt your credibility if you submit an article with multiple spelling errors and typos to a professional forum. Just takes a second to run it through a spell checker.Thu, 18 Jun 2009 19:07:01 GMTdaegubooghttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxIf you are upgrading SQL 2005 to SQL 2008 or an earlier version of SQL 2005 to a newer version (such as SP1 to SP3), then you can do this with database mirroring. Then you don't need to do the tail backup or any of the manual steps when it comes time to switchover. You just need to perform a failover.Best part is, you can update the client conenction strings ahead of time to have the new server as the principal and the old server as the mirror and the client will automatically switch to the new machine.And if you want, you could then upgrade the old server and keep using it as a mirror for the new server.Thu, 18 Jun 2009 16:55:23 GMTRobert Davishttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxVery good information. Thank you for sharing.Thu, 18 Jun 2009 16:00:27 GMTCameron Mayfieldhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxI'd probably add a step at the end.After the applications are up and you're sure they're all pointed at the new server and working fine, bring the old database back on-line (or restart the old SQL Server), and perform a final full backup. This is useful for ensuring you have a copy of the database as it existed in its old environment at the time of cutover. As you noted, sometimes the new database may not be restorable on the old server (different SQL versions). And depending on auditing requirements, you may need a restorable backup for off-site storage. It also gives you a picture of the data as it existed at the point of cutover.Thu, 18 Jun 2009 15:58:20 GMTRonzohttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxGood job Jonathan :)Thu, 18 Jun 2009 15:26:35 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]noeld (6/18/2009)[/b][hr]Ermm... Isn't this just a description of LogShipping ?[/quote]Yep. it is.Thu, 18 Jun 2009 08:14:44 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]noeld (6/18/2009)[/b][hr]Ermm... Isn't this just a description of LogShipping ?[/quote]Yes it is, using a manual log shipping process to keep a large db up to date prior to the migration date. I used this many times and it does save alot time compared to 'doing it all the day'Thu, 18 Jun 2009 08:00:25 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxErmm... Isn't this just a description of LogShipping ?Thu, 18 Jun 2009 07:48:29 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspx[quote][b]Noel McKinney (6/18/2009)[/b][hr]Good article Jonathan, I've used these steps to move databases to new hardware and datacenter with just a few minutes of downtime. The key is making sure that the application(s) that use the databases are good to go. If possible you need to test such applications on the new hardware first to make sure the configurations are set properly... restore a backup of an actual database for such testing, using a "test" database might not reveal potential problems.The other thing is to make sure that logins have been propagated to the new server.[/quote]The sp_help_revlogin stored proc could be useful for moving the logins.edited to add URL:http://support.microsoft.com/kb/246133Thu, 18 Jun 2009 07:41:06 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxwe're usually not as niceif we do a side by side upgrade we tell people the cut off for R/O access. then we backup and restore on the new hardware. critical databases are on the SAN and we just point people to a replicated copy of the data while we upgrade the hardwareThu, 18 Jun 2009 07:30:36 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxGood article Jonathan, I've used these steps to move databases to new hardware and datacenter with just a few minutes of downtime. The key is making sure that the application(s) that use the databases are good to go. If possible you need to test such applications on the new hardware first to make sure the configurations are set properly... restore a backup of an actual database for such testing, using a "test" database might not reveal potential problems.The other thing is to make sure that logins have been propagated to the new server.Thu, 18 Jun 2009 06:59:07 GMTNoel McKinneyhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxI have used this method for years. Even with busy and/or large databases, I can move a database server to a new piece of hardware with only about 10 minutes of downtime. I recently used the same method during migration of a number of legacy SQL 2000 boxes to SQL 2005.Thu, 18 Jun 2009 05:59:14 GMTpublicbutler@imapmail.orghttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxThanks for the article!Just a thought...steps 1-7 can be done using setting up log shipping and the rest can be done using step 8 onwards when moving on to the new server.Thu, 18 Jun 2009 05:59:02 GMTAmit Whttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxI sincerely appreciate this idea Jonathan...Thu, 18 Jun 2009 04:00:37 GMTJagadish Kumar Punnapuhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxThanks for the good article, Jonathan. :-)Thu, 18 Jun 2009 02:57:30 GMTPyay Nyeinhttp://www.sqlservercentral.com/Forums/Topic737175-1365-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Backup+%2f+Restore/66962/">Use Backup/Restore to Minimize Upgrade Downtimes</A>[/B]Thu, 18 Jun 2009 00:07:40 GMTJonathan Kehayias