Moving a large number of databases from one server to another?
-
What would be the best way to move 50 databases from my old server to my new server?
-
mmcbride 2735 (5/3/2016)
What would be the best way to move 50 databases from my old server to my new server?Depends on what tolerance you have for a downtime, easiest would be to switch the file system from the old to the new or copy the files from one to the other but that requires downtime.
😎
Can you elaborate further and describe the full requirements, setup etc..
-
could you not just do backup and restore ? Are they the same version of sql server ? If not, have you tested ?
-
Backup and restore works fine.
Detach and reattach works fine (beware the stats updates though - make sure you tell the script to not do those).
If this is a migration and you need to minimize downtime on the application(s) then you can set up tlog shipping or database mirroring to help facilitate quick switch-over. Note that mirroring can have a significant overhead.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (5/3/2016)
Backup and restore works fine.Detach and reattach works fine (beware the stats updates though - make sure you tell the script to not do those).
If this is a migration and you need to minimize downtime on the application(s) then you can set up tlog shipping or database mirroring to help facilitate quick switch-over. Note that mirroring can have a significant overhead.
+1 on mirroring. As long as you are standard -> standard or enterprise -> enterprise, it is a phenomenal way to migrate, even across versions. We had a central DB system we had to move from a 2008 to a 2012 environment, with replication jobs all coming from warehouses all over the country. Central site was the central subscriber; each site had its own central database on the instance. We just set up mirroring to all central DBs from 2008 to 2012 to the new location. Migration was just simply this -- total down time for 2 hours in all warehouses; delete subscription in warehouse DB to central database by database, fail over central DB to new server, create subscriptions without init, and done. The sites all had all the data, just needed to move. Break the mirror post-migration and all was well. When it went from 2000 to 2008 several years earlier, it took two DBAs 20 hours each without mirroring for all the restores and setup. Same crew did it in 2 hours the way I describe from 2008 to 2012.
-
Considering you have physical server and storage is SAN box.
If you are going to use the same storage and all your data and log file in SAN box. Build your new server and configure the Logins and system db's then ask server team to attach the storage from old server to new server.
After the storage attached, You can Attach the db's.
-
1.Right-click on Database -> select 'Tasks' -> select 'Generate Scripts'
2.Select database objects. Click Next
3.Click Advanced and scroll down to 'Types of Data to script' and choose 'Schema and Data'. Click OK
4.Choose where to save generated script and proceed by clicking Next
-
Hi Sorry for lack off information.
All my application databases are on sql server 2012 but need to move them to the new vm I have created on the same network.
-
mmcbride 2735 (5/4/2016)
Hi Sorry for lack off information.All my application databases are on sql server 2012 but need to move them to the new vm I have created on the same network.
Why are you still asking questions? We gave you multiple simple options and some ones that assist with minimal downtime if desired. You didn't add anything useful with this post, so aren't we done? 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
johnwalker10 (5/3/2016)
1.Right-click on Database -> select 'Tasks' -> select 'Generate Scripts'2.Select database objects. Click Next
3.Click Advanced and scroll down to 'Types of Data to script' and choose 'Schema and Data'. Click OK
4.Choose where to save generated script and proceed by clicking Next
While I like and use this technique, I've seen an upper limit to it. With big databases, and I don't know how big, it seems to silently fail. So I'd use it with caution (and LOTS of testing!) against production systems.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
HI,
Been flat out thanks for all your help Guys appreciate your advice
-
PearlJammer1 (5/3/2016)
could you not just do backup and restore ? Are they the same version of sql server ? If not, have you tested ?😀
-
Log shipping would be good and easy to to keep the minimal downtime to swithover.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
As others recommended is good.
I just moved mine by backing up, restoring to the same version of SQL Server from on-premise to a data center. Transfer happened over a period of time, larger data was shipped via device to save time. Once everything was in one location, environment was spun up, databases loaded and then we later upgraded from there to latest versions.
Being no one has mentioned it yet, if you do transfer, note the bandwidth usage if applicable. We had multiple lines and we dedicated a full line/circuit to the transfer with help of the network admins.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply