March 24, 2017 at 4:00 pm
For the same migration project of moving
2 TB and 500 databases If I feel
Data doesn't change and traffic is stopped by the firewall and network guys, will taking full backup and restoring at the destination be enough I mean no log backups no differential backups
March 25, 2017 at 6:26 pm
My recommendation stands as it did on the other thread.
1. I assume you're doing normal full backups at the very least. If they're not being done daily, that's fine but run all full backups no earlier than the day before you're ready to do the migration.
2. As soon as the backups are done for step #1 above, begin and complete the full restores with NO RECOVERY.
3. About an hour before you want to complete the migration, turn off any log file backups and do a DIF backup for all of the databases.
4. Once step 3 is complete, immediately begin the restore of the DIFs. Again, use with NO RECOVERY.
5. When you're ready to complete the migration, do TAIL LOG backups of the databases. This will set each of the source databases to a "Recovery" mode where no one can make any additions or modifications. The "downtime" clock starts at the beginning of this step.
6. As soon as step 5 completes, immediately begin doing the restores of the log files. This time, use WITH RECOVERY. The "downtime" clock ends at the end of this step.
This will minimize your downtime to just several minutes rather than the hour or more that your proposed "FULL backup only" plan would entail. It will also guarantee that no one has slipped something into your databases because your network guys can't actually guarantee that someone local or a local job won't slip something in.
Obviously, you want a script that does each step for you. Using a little dynamic SQL, it should all be fairly easy.
If you try to shortcut around this, one of two things will happen... maybe both.
1. The total downtime will be a lot longer than it needs to be.
2. You can't guarantee that you've gotten every bit of data because of what I said before.
To answer the question expressed in the title of the thread, the answer is "No"... or at least in my opinion.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2017 at 7:55 pm
Shifting gears a bit, you've basically posted the same question different ways for the same problem 3 times now. Here are the other two.
https://www.sqlservercentral.com/Forums/1866664/Tlog-backups-indicate-no-data-change
https://www.sqlservercentral.com/Forums/1865369/Migrating-Databases-from-old-version-to-new-version-in-shortest-time
You're splitting up the answers you've already been given for the same problem. I suggest that you keep it all together in a single post from now on. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2017 at 8:18 pm
Make that a total of 4 posts on the similar subject now.
https://www.sqlservercentral.com/Forums/1862600/moving-several-large-databases-from-old-hardware-Live-to-new-hardware
Seems like we've gone from 40 databases to 500 database to 1200 databases back to 500 databases.
It doesn't matter what the size of the databases are nor how many there are... the answer is the same.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2017 at 8:27 pm
Whup! Lookie there! Yet another post on the same subject more than a month ago.
https://www.sqlservercentral.com/Forums/1858895/Upgrading-from-SQL-Server-2008R2-Std-Ed-on-32-bit-hardware-to-SQL-Server-2012-Ent-64-bit-on-64-bit-h
What really sucks about this is that Joe Pattyn gave the correct answer even way back then even though the seemingly variable number of databases on that post was 200. Why did you decide to ignore that and continue to ask the same question in different ways 4 additional times?
My recommendation is that you implement what you've been told and stop asking the question because you've spent more time asking the same question than you have implementing. Git'er done man! 😉
p.s. It's not likely that we'll write the code for you because we don't know your servers or your backups. This is something that you're going to have to do. Like I said on one of the many other posts on this same subject, start out small and do some tests in a smaller environment even if you have to just make a couple of test databases.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2017 at 8:34 pm
OMG!!! Make that 6 posts on the same subject.
https://www.sqlservercentral.com/Forums/1861649/Moving-2-TB-600-databases-from-32-bit-SQL-Server-2008-R2-to-64-bit-SQL-Server-2014-from-Friday-night
You now have the steps to take. Just do it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 25, 2017 at 9:26 pm
Thanks Jeff for the responses. Since its a major project I wanted to get all the input and all doubts sorted out.
Finally on the big day, I just took a full backup and restored it ( Stopped SQL Server and made sure the network guys turned off all traffic ) . after 15 min, I started SQL Server waited for few and then confirmed that there are no connections and finally ran FULL BACKUP of ALL databases. NO T log backups No Diff backups. In the restore script I had to change all drive letters use MOVE OPTION and it worked like a charm,
The destination box was also on same datacenter, so copy was faster and finally I verified that all DB's and size matched the source. The network guy said flash drives and high throughput.
Before restoring the user databases, on destination I transferred logins, Along with logins the security roles for the server level princiapls ( This was missed in Dev and we had lots of issues with errors as if app login never existed though it was ported - SERVER ROLES need to be scripted and moved as well which no one talks about, then msdb jobs, master and msdb special objects which existed in the source ( just scripted and compared with 3rd party tool ).
There are great scripts to generate RESTORE out there on google, which basically get all the paths from msdb backup set and I just used the script on the new box and restores were easy.
Thank you again for taking the time to respond in detail. Appreciate your willigness to share your knowledge!
March 25, 2017 at 10:44 pm
Also to mention the last minute issues from other team members who had the backup drive for 2 TB but did not have the data drive till the last minute.
With all these challenges, Full backup and Restore alone was the best option.
March 26, 2017 at 9:33 am
sqlguy80 - Saturday, March 25, 2017 10:44 PMAlso to mention the last minute issues from other team members who had the backup drive for 2 TB but did not have the data drive till the last minute.With all these challenges, Full backup and Restore alone was the best option.
Heh.... wow. I guess management didn't mind having a couple of hours of downtime. I feel for you. When we went through it, total downtime was measured in minutes rather than hours because management saw the value in preparation like having data drive space available for the migration. They knew that we'd be able to repurpose the hardware from the old box when we verified that everything was working correctly and we didn't need to flop back to the old system.
On the subject of roles, jobs, and packages (SSIS, SSRS, whatever), I made the assumption that you already knew about all of that and that you were only interested in minimizing the total downtime for the database migrations. My apologies for that assumption.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply