Backup and Restore a SQL database with a two line script

Here is a simple method to backup and restore a database from Server A to Server B using dbatools. The script will backup DB1 from Server A and restore the backup onto Server B as DB2.

Please note that the SQL service account on both servers need to have access to the network shared folder in order for this to work.

Backup script:

Backup-DbaDatabase -sqlinstance ServerA -database DB1 -path \\NetworkShare\SQLBackup\ServerA -copyonly -compressbackup

Restore script:

Restore-DbaDatabase -SqlInstance ServerB -Path "\\NetworkShare\SQLBackup\ServerA\DB1_202201261123.bak" -UseDestinationDefaultDirectories -DatabaseName DB2 -ReplaceDbNameInFile

However, if you ever need to simply copy a database DB1 from Server A to Server B. You can use the below script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy one database
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Database DB1 -SharedPath \\NetworkShare\SQLBackup\ServerA

If you need to do a full-on migration of databases from Server A to Server B, here is the script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy all databases
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Alldatabases -SharedPath \\NetworkShare\SQLBackup\ServerA

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: