I’ve been working on a project this year to upgrade SQL Server versions for around 80 application databases, with most of the upgrades requiring both SQL Server and Windows Server upgrades to get to the future state we were looking for. The general process for each of these was to build a new virtual machine with the upgraded operating system, install the desired SQL Server version and then migrate the application databases during an arranged downtime window.
I’m going to focus on the final step of this process for this post – migrating the databases during the downtime windows. Luckily for me, dbatools made this both easy and repeatable.
Step 1 – Check for connections
First step when we get into the downtime window is to check whether there are any active connections to the database you want to migrate. We don’t want any data being changed while we migrate, there’s a command for that:
Get-DbaProcess -SqlInstance SourceServer -Database MigratingDatabase | Select Host, login, Program
If there are connections and it’s safe to remove them (if they are still coming from the application it might be worth talking to the app owners first) you can pipe them to another handy dbatools command:
Get-DbaProcess -SqlInstance SourceServer -Database MigratingDatabase | Stop-DbaProcess
Step 2 – Migrate the database
Now that there are no connections we can move the database. Depending on the situation it might be worth setting the database to read only or single user mode first. In my case, I had the application taken down so I felt confident no connections would be coming in.
With one line of code we can select the source and destination servers, the database name, specify that we want to use the backup and restore method, and then provide the path to a file share that both instance service accounts have access to:
Copy-DbaDatabase -Source SourceServer -Destination DestinationServer -Database MigratingDatabase -BackupRestore -SharedPath \\fileshare\
There are a lot more options available on this command, including setting the number of backup files to use, which can speed things up if you have a large database. I recommend checking out the command based help for all the available options.
Step 3 – Migrate the user logins
Once the database is on the new server we can use the following to copy the associated logins across. The nice thing about using this command is it ensures the user SIDs match up on the destination and you don’t end up with any orphan SQL Logins.
Copy-DbaLogin -Source SourceServer -Destination DestinationServer -Login AppReadOnly, AppReadWrite, DOMAIN\AppUser
Step 4 – Set the source database offline
Now that the database and associated logins have been migrated we can set the source database offline. I did this so if there were any issues getting the application up we could quickly revert back while ensuring nothing was still accessing the old copy.
Set-DbaDbState -SqlInstance SourceServer -Database MigratingDatabase -Offline -Force
In the end I was able to use 5 lines of PowerShell to get these application databases migrated to their new homes. After some testing I dropped the old offline copy of the database and eventually decommissioned the old servers.
I hope this gives you some ideas of how dbatools can help make your database migrations easier and more efficient.
Jess 🇬🇧
Pingback: Database Migration With dbatools – Curated SQL
Pingback: T-SQL Tuesday #101 – The Multitool of my DBA toolbox – Jess Pomfret
Pingback: T-SQL Tuesday #102 – Giving Back – Jess Pomfret
Hi,
I’ve just begun the process of migrating / consolidating our many instances on 2 new clusters with dbatools as my goto tool.
I have one case where developers want to bring a prod database down to stage and dev on a regular basis, copying over the existing databases each time. Can you recommend a process using dbatools?
Hey Todd,
Glad you are using dbatools for that process! For bring production data down to non-prod I would use something similar to this process, you could also look at `Invoke-DbaDbDataMasking` to mask any sensitive data (https://docs.dbatools.io/#Invoke-DbaDbDataMasking). There is also a post on scheduling dbatools scripts that might help if you wanted to run it automagically https://dbatools.io/agent/.
Thanks for stopping by,
Jess
Hi Jess,
These tools are fabulous. Thanks for all of the hard work of you and your team.
I’m trying to migrate a database from a newer SQL server to an older one. Unfortunately, I don’t have the option to change the version of SQL server in use.
When I attempt to do a restoration of the backup, I get an error saying that it’s incompatible. Is there a way around this short if using a script to call through each table and copy everything row for row?
Michael
Hi Michael,
You can’t backup and restore from new to old, as you have discovered. The only way to do that would be as you say moving data. dbatools can help you with this, it has commands to copy table data and generate scripts. Copy-DbaDbTableData, Export-DbaScript. Or if you have an older compatible backup you could restore that (then maybe merge everything). BUT – it sounds like you have your database on a newer version of SQL now, can’t you just keep it there?
If for some reason you want to keep new and old, can’t think why but who knows. You could use SQL server replication to keep the old server in sync with the new server (assuming its not more than 2 versions older, I think it’s 2 version max jump for replication)
Good Luck!
This is a very good function, but one question, if my destination db needs to be a different folder (from the source db folder), is this doable?
An example is:
source db: X_DB, two files d:\data\x.mdf and e:\log\x.ldf
want to restore to destination db as follows
destination db: Y_DB, two files M:\data\x.mdf and N:\log\x.ldf
is this doable? (also assume M:\Data\ and N:\Log\ are NOT the default db paths at the destination server side)
Is there a way for the command to use the credential that I give it instead of the service account for the backup/restore?
Unfortunately, our systems are so segregated they don’t share directories, nor do they share a service account. Since I am trying to copy databases in the same environment I have access with my DBA credentials, but they don’t seem to be used anywhere I need them to be used (aka the file system to access the .bak).
I was surprised it was using the service account not the one I provided for -DestinationSqlCredential/-SourceSqlCredential
Here is the warning:
WARNING: [15:48:47][Test-DbaBackupInformation] Backup File \\SERVERA\backup$\A_DB_201910221548-2-of-3.bak cannot
be read by SERVERB. Does the service account (TSTDOMAIN\SERVERBSVCACCT) have permission?