August 31, 2018 at 3:08 am
I have a SQL Cluster with Multiple databases. some small and large spanning multiple NDF Files.
My question is this. I have migrated databases in the past (Backup and Restore) however i am curious to know given my example above is there a preferred solution for migrating databases if the versions were like for like? I am Just looking to ensure i am working as close to best practice as possible and keen to see peoples views.
Many thanks peeps.
September 4, 2018 at 7:11 am
This was removed by the editor as SPAM
September 4, 2018 at 2:11 pm
Andrew.weckermann - Friday, August 31, 2018 3:08 AMI have a SQL Cluster with Multiple databases. some small and large spanning multiple NDF Files.My question is this. I have migrated databases in the past (Backup and Restore) however i am curious to know given my example above is there a preferred solution for migrating databases if the versions were like for like? I am Just looking to ensure i am working as close to best practice as possible and keen to see peoples views.
Many thanks peeps.
Backup and restore is fine for moving databases. You don't need to buy a migration tool for this - it's a waste of money.
Sue
September 5, 2018 at 7:02 am
Sue_H - Tuesday, September 4, 2018 2:11 PMAndrew.weckermann - Friday, August 31, 2018 3:08 AMI have a SQL Cluster with Multiple databases. some small and large spanning multiple NDF Files.My question is this. I have migrated databases in the past (Backup and Restore) however i am curious to know given my example above is there a preferred solution for migrating databases if the versions were like for like? I am Just looking to ensure i am working as close to best practice as possible and keen to see peoples views.
Many thanks peeps.
Backup and restore is fine for moving databases. You don't need to buy a migration tool for this - it's a waste of money.
Sue
Also don't forget to migrate Logins to match those users in your restored database.
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
September 5, 2018 at 8:19 am
Smendle - Wednesday, September 5, 2018 7:02 AMSue_H - Tuesday, September 4, 2018 2:11 PMAndrew.weckermann - Friday, August 31, 2018 3:08 AMI have a SQL Cluster with Multiple databases. some small and large spanning multiple NDF Files.My question is this. I have migrated databases in the past (Backup and Restore) however i am curious to know given my example above is there a preferred solution for migrating databases if the versions were like for like? I am Just looking to ensure i am working as close to best practice as possible and keen to see peoples views.
Many thanks peeps.
Backup and restore is fine for moving databases. You don't need to buy a migration tool for this - it's a waste of money.
Sue
Also don't forget to migrate Logins to match those users in your restored database.
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
Actually, I find this to be far simpler and less troublesome to transfer logins:
https://dbatools.io/
The function is Copy-DbaLogin
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/
September 6, 2018 at 5:36 am
Sue_H - Tuesday, September 4, 2018 2:11 PMAndrew.weckermann - Friday, August 31, 2018 3:08 AMI have a SQL Cluster with Multiple databases. some small and large spanning multiple NDF Files.My question is this. I have migrated databases in the past (Backup and Restore) however i am curious to know given my example above is there a preferred solution for migrating databases if the versions were like for like? I am Just looking to ensure i am working as close to best practice as possible and keen to see peoples views.
Many thanks peeps.
Backup and restore is fine for moving databases. You don't need to buy a migration tool for this - it's a waste of money.
Sue
for larger databases it may be quicker to offline the current database and copy the files then attach to the destination, assuming you have an outage planned
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 10, 2018 at 6:48 am
Also, large databases can be migrated with minimal downtime if you do it in two steps:
Before taking down any applications, make a full database backup of your source database and restore that WITH NORECOVERY on your destination server.
When this is done, THEN you shut down applications or lock them out of your database, make a DIFFERENTIAL backup of your source database and restore that to your destination server WITH RECOVERY.
If you know you are moving the database and it's not supposed to be online on the source after you're done with it, you can use something like this to make the final differential backup:
ALTER DATABASE [yourDBnamegoeshere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP DATABASE [yourDBnamegoeshere] TO DISK = 'your\backup\path\goes\here' WITH DIFFERENTIAL
ALTER DATABASE [yourDBnamegoeshere] SET OFFLINE
First line puts the database in single user mode, effective immediately.
All active connections will be terminated immediately and any open transactions will be rolled back.
Of course, a more graceful exit from the user side is preferrable if it can be arranged, but this way you won't get stuck "Suspended" waiting for every last user process to either get killed or leave on their own accord.
Alas, as so many have learned the hard way. single user mode just means that only 1 user can access the database - there is no filter on WHICH user this 1 should be - so in order for this to work the way you intend it to, it is crucial that YOU grab hold of that DB before anyone else does. The key to ensuring this is to run both commands in the same script: You set the DB in single user mode - and then you immediately proceed to start the backup, which will then use the only available DB connection - and no one else will be able to mess with the DB after that.
The final line sets the database source database offline after your backup has finished so that it can only be accessed in one place: You'll bring it online at the destination after restoring the backup you just made!
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
September 10, 2018 at 6:59 am
I'd look at dba tools, it was built for this and has lots of options to help.
September 10, 2018 at 10:39 am
Perry Whittle - Thursday, September 6, 2018 5:36 AMfor larger databases it may be quicker to offline the current database and copy the files then attach to the destination, assuming you have an outage planned
Yup....anything other than buying a third party product which was more the message I was concerned about.
Sue
June 5, 2020 at 7:06 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply