I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online.
But: The old server is connected via a 200 mbit uplink only, which means it would take me more than 1 hour to move the whole thing. That's too long to be down for my situation so I'm wondering if it might be suitable to just set up transactional replication and on day x take the old one offline, change my DNS records to the new server and make that one the new writeable master.
Can I be sure that all my table settings, views, etc. are properly copied to the new machine?
Or is there another suitable way to avoid longer downtimes when moving databases?
Best Answer
Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...