Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rternier
Starting Member
44 Posts |
 Posted - 2009-06-01 : 17:44:05
|
| I have 12 databases that are over 30GB in data each.I can't shrink them because they container over 100 million rows of raw numerical data.We are moving these databases off of our main production DB onto a new one, however they are not in the same network so transfer will be about 30 hours over FTP.What I want to do is leave the data behind, and create the DB's fresh on the new server so the applications using that data won't be interupted. They won't have the historical data right away.After it's all up, i'll spend the 30 hours FTPing the old data to the new server, attach the databases as backups and run a script to copy the data over.Is that the right way of doing this?We cannot do any data transfer between the 2 boxes as we cannot have a down time of more than 30 minutes.Customers don't need the historical data right away, but it does need to be there.Thanks,----Killer ASP.NET ninja coding monkeys do exist![url]http://weblogs.asp.net/rternier[/url] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-06-01 : 17:52:51
|
| I would go down a different approach:1. Run full backups2. Compress the backup files to zip or rar format3. Copy the compressed files to the destination server4. Do transaction log backups for the next several hours5. Uncompress the backup files6. Restore the full backups7. Copy and compress the tlog backups8. Restore the tlog backupsWith this approach, you wouldn't have much downtime at all. The only downtime you would need is for the final transaction log backup and then copy/restore that final file. You should be able to measure that downtime in just a few minutes.Do the transaction log backups often enough that the file sizes are small.By the way, 30GB databases are considered small these days.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
rternier
Starting Member
44 Posts |
Posted - 2009-06-01 : 17:58:32
|
| Will the backup and compression shrink the databases small enough to FTP between the servers? the connection will go out to the internet and then route back in.(We can't link the servers together).----Killer ASP.NET ninja coding monkeys do exist![url]http://weblogs.asp.net/rternier[/url] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
transfering large DB's between servers