I had to setup a new server and I needed to copy all of the databases from the old server to it. This can take some time if you have many databases. Using Copy-DbaDatabase from dbatools makes this a simple process.
Read more: Copy all databases and logins to a new server using: Copy-DbaDatabase and Copy-DbaLoginIf you have not seen any of my other posts about dbatools, please follow this link to see how to set it up in your environment and to see the benefits of what it can do for you. https://jimsalasek.com/2021/10/05/dbatools-io-a-dba-timesaver-luxury/
Using just one command, you can copy all of the databases from one server to another. It can use different methods, but I am going to use the USELASTBACKUP method. From the documentation: “This uses the last full, diff and logs instead of performing backups. Note that the backups must exist in a location accessible by all destination servers, such a network share.”
Here is the command I used to complete this task. I had to supply my source and destination server names. Since these servers were on a different network, I had to specify credentials. If I was using Windows Authentication, I would not have needed to supply credentials. The command below pops up a box for you to type in your password.
Copy-DbaDatabase -Source SERVERNAME\INSTANCE -Destination SERVERNAME\INSTANCE -BackupRestore -UseLastBackup -Force -SourceSqlCredential sa -DestinationSqlCredential sa -AllDatabases
When it is all complete, your screen should look like below with all showing a Successful status.
Looking in SSMS, it shows all of the databases being on the new server.
Now another very useful command from dbatools, is copying over all of the usernames/passwords from the old server to the new server. This is also a one line command. Using Copy-DbaLogin, you do not need to worry about orphan logins which can happen when restoring databases onto a new server. From the documentation: “SQL Server 2005 & newer: Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc.)”
Copy-DbaLogin -Source SERVERNAME\INSTANCE -Destination SERVERNAME\INSTANCE -SourceSqlCredential sa -DestinationSqlCredential sa
This will skip any user that already exists on the server.
Using dbatools and the commands Copy-DbaDatabase and Copy_DbaLogin migrating to new servers is a breeze. To read more about these commands please follow the links below. https://docs.dbatools.io/Copy-DbaDatabase.html
Jim Salasek's SQL Server Blog 