Hi DBA's,
We have a situation here our application is working on SQL server database on a cluster environment. Now we wish to migrate the database to a standalone environment, we have approx 2850 Database with size 1.6 TB. The DBA has said it is not possible to migrate that many database as they need to Export/Import one database at a time so 2850 will take hell lot of time. Do you thing is this the only way to perform migration in SQL server or there is more innovative way of performing this operation.
Thanks
Varun Bahal
I don't know if there is a "smarter" way, but I've done something similar - hundreds of DBs rather than thousands, and much smaller - and we just scripted a RESTORE on the new server. All our DBs are Full Recovery Model, so we were able to restore on new target server whilst the original server was still in use, then "disable" the Source database, take a tail log backup and restore that on Target server and make it active (and then change all connections for Clients)
yeah, it "took a while"
Can you help me with sample script how it was done
Sorry, don't think I have anything lying around. Some links:
Overview: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172279#674666
Fuller detailed steps: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44537#145142
If you have a large enough downtime window you could STOP the SQL Service (on the source server) - that will take out ALL databases on that server, so if that is not acceptable you could, instead, mark the ones you are going to copy OFFLINE - then COPY all the raw database files to the Target server, and ATTACH them (instead of using Backup/Restore).
That saves the Backup / Restore process, but physically copying all the raw DB files will take some time, and the database / users are offline for the whole time. The Backup/Restore method can be done with less than 5 minutes of downtime, for each database, if you need very high up-time.
P.S. You also need to take care of all Logins, Jobs and so on.
If you are willing to run a single-node cluster after the migration - then all you need to do is add the new node to the cluster, present the shared storage to the new node, install SQL Server into the cluster on the new node (patch to same level) - then move SQL Server to the new node.
Once up and running on the new node - remove SQL Server from the old nodes and evict the old nodes from the cluster.
Downtime is limited to how long it takes SQL Server to start and bring all databases online. Since you still have a cluster - you have options to add new node as a FCI node (shared storage) - or add nodes as Always On (non-shared storage).
Another quick approach to migrate SQL Server database from one server to another is to try SysTools SQL Server Database Migrator which is capable to transfer your data directly to SQL Server or move tables data between database to database.
Back and restore in an automated fashion, use script tools such as powershell. try it with a few dozen then move to 24 etc. wash rinse repeat.