May 18, 2015 at 8:42 am
I am going to migrate all the databases (70+) from one sql08 server to another sql08 server, and I am wondering the most effective way to keep users from updating the databases while performing the backups. I am not sure about using single users as there are so many databases, and also considering pulling the lan connections so nobody can talk to it while I am running the backups. Does anyone have a better/more efficient solution for this?
Thanks in advance.
Brian
May 18, 2015 at 8:48 am
Instead of doing a backup, detach, copy and re-attach.
This should be faster, plus, as soon as you detach the database will become unavailable.
Use caution, however, there may be some transactions that you will kill when you detach.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 18, 2015 at 8:52 am
I like that idea, but will need to script it to do all databases at once, in scripts as there are so many databases. Then script the attach as well. Let me look into that.
May 18, 2015 at 12:45 pm
when doing a migration previously, i toggled all databases to read only before i backed them up, and left the old server accessible but readonly so they could be used but not updated.
then i restore on the new server, and toggled the database sot read-write.
Lowell
May 18, 2015 at 1:33 pm
Well, if the databases are in the full recovery model, you could take tail log backups, and then it'll be tough for the users to update any data 🙂
My preferred migration method is mirroring, since the downtime is then just the time it takes for the mirror to failover and any connection strings to be changed (no copying data files or backup files around while down).
With that many databases though, mirroring would best be done in batches.
Cheers!
May 18, 2015 at 1:46 pm
Try this script.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 18, 2015 at 2:14 pm
Thanks for all the great advice and scripts. I decided I was going to go with the option of scripting the detach/attach and then just transfer the data and log files to a temp drive, then attach temp drive to new server and run attach script. I did leave out the part (sorry) that the new server will be renamed (and IP) the same as the old server since there are so many apps pointing to that box that would require redirecting. I tested moving those data and log files to the temp drive (SAN), and wow it took a lot longer than backing up the databases to the same drive (size difference). With that being said, I will probably go the route of backing up the databases to the temp drive, then restoring from the same temp drive on new server (renamed as old). So - with that being said - if I restart the SQL service (to kill all connections) and then alter the startup to start the instance in single user mode, will I be able to use ssms like normal? I will test this, but cannot until tonight, so I was just curious. I have not set a whole instance to single user mode before.
Thanks,
Brian
May 18, 2015 at 3:38 pm
Rather than starting the server in single-user mode, which is bound to cause grief, disable TCP/IP and named pipes and run all from SSMS on the server.
Or set all databases as restricted_user - that works as long as applications does not connect as sysadmin or db_owner.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 18, 2015 at 5:29 pm
To pile on to Erland's suggestion, run a script that disables all logins except your own.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 19, 2015 at 9:24 am
I agree with a previous suggestion, in that you change the databases to read only.
before the switch over and while the databases are still active (READWRITE) you can perform the most time consuming step of taking a full backup of each db and restore to the new server with norecovery. also copy over jobs, users etc. when you are ready for switch over, change the databases on the current live server to readonly, then take differential backups and restore to the databases on the new server with recovery. this method will reduce downtime.
May 19, 2015 at 9:42 am
When choosing between detach-attach and backup-restore, bear in mind that if there's a lot of free space in your database or log files, it could take a long time to move the files. The fact that you can back up with compression (SQL Server 2008 Ent Ed or SQL Server 2008 R2 Standard Ed or later) makes it more likely that backup-restore will be the better choice.
John
May 19, 2015 at 10:16 am
Yes - I do agree. This will give a good opportunity to free up some space as well with the full backup/restore method, and faster as well. I think I will also set the databases to read only as well and back them up and then bring the server down and then bring up the new one, rename it, run instance update script, then restore.
I will research scripting read-only mode for ALL database now. I have not had to deal with a large group of databases on one instance in the past. New fun things. 🙂
May 19, 2015 at 10:44 am
Brian Seib (5/19/2015)
Yes - I do agree. This will give a good opportunity to free up some space as well with the full backup/restore method,
Not sure what your expectations are here. If the log file is 20 GB on the source server, it will be 20 GB on the destination server as well. But the backup will not include 20 GB of dead meat as when you detach/attach, so you will save some time on copying. But the 20 GB has to be zeroed out.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply