Voor een klant mocht ik een server migratie uitvoeren (oude SBS omgeving naar Server 2019).
Op de oude SBS server stond een SQLExpress instance geïnstalleerd met meer dan 300 databases 
Dit wilde ik natuurlijk niet met het handje overzetten om RSI te voorkomen 
In het kort heb ik hier 2 handige scripts voor gebruiken, 1 script om van alle databases (exclusief systeemdatabases) een .bak te creëren en 1 script om al deze .bak bestanden te restoren.
Hieronder het SQL export script:
Copy to Clipboard
x
1
DECLARE @name VARCHAR(50) -- database name 2
DECLARE @path VARCHAR(256) -- path for backup files 3
DECLARE @fileName VARCHAR(256) -- filename for backup 4
DECLARE @fileDate VARCHAR(20) -- used for file name5
6
-- specify database backup directory7
SET @path = 'D:\SQLBackups\' 8
9
-- specify filename format10
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 11
12
DECLARE db_cursor CURSOR READ_ONLY FOR 13
SELECT name 14
FROM master.dbo.sysdatabases 15
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases16
17
OPEN db_cursor 18
FETCH NEXT FROM db_cursor INTO @name 19
20
WHILE @@FETCH_STATUS = 0 21
BEGIN 22
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 23
BACKUP DATABASE @name TO DISK = @fileName 24
25
FETCH NEXT FROM db_cursor INTO @name 26
END 27
28
29
CLOSE db_cursor 30
DEALLOCATE db_cursorVoor het restoren van alle databases heb ik onderstaand PowerShell script gebruikt:
Copy to Clipboard
45
1
$backupRoot = Get-ChildItem -Path "D:\BAK_FILE_FOLDER"2
$datafilesDest = "D:\DATA_FILE_FOLDER"3
$logfilesDest = "D:\LOG_FILE_FOLDER"4
$server = "server\inst" 5
6
## For each folder in the backup root directory...7
#8
foreach($folder in $backupRoot)9
{ 10
# Get the most recent .bak files for all databases...11
$backupFiles = Get-ChildItem -Path $folder.FullName -Filter "*.bak" -Recurse | Sort-Object -Property CreationTime -Descending | Select-Object -First 112
13
14
# For each .bak file...15
foreach ($backupFile in $backupFiles)16
{17
# Restore the header to get the database name...18
$query = "RESTORE HEADERONLY FROM DISK = N'"+$backupFile.FullName+"'"19
$headerInfo = Invoke-Sqlcmd -ServerInstance $server -Query $query20
$databaseName = $headerInfo.DatabaseName21
22
# Restore the file list to get the logical filenames of the database files...23
$query = "RESTORE FILELISTONLY FROM DISK = N'"+$backupFile.FullName+"'"24
$files = Invoke-Sqlcmd -ServerInstance $server -Query $query25
26
# Differentiate data files from log files...27
$dataFile = $files | Where-Object -Property Type -EQ "D"28
$logFile = $files | Where-Object -Property Type -EQ "L"29
30
# Set some variables...31
$dataFileName = $dataFile.LogicalName32
$logFileName = $logFile.LogicalName33
34
# Set the destination of the restored files...35
$dataFileFullPath = $datafilesDest+"\"+$dataFileName+".mdf"36
$logFileFullPath = $logfilesDest+"\"+$logFileName+".ldf"37
38
# Create some "Relocate" file objects to pass to the Restore-SqlDatabase cmdlet...39
$RelocateData = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $dataFileName, $dataFileFullPath40
$RelocateLog = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile, Microsoft.SqlServer.SmoExtended, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' -ArgumentList $logFileName, $logFileFullPath41
42
# Perform the database restore... and then go around the loop.43
Restore-SqlDatabase -ServerInstance $server -Database $databaseName -BackupFile $backupFile.FullName -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase44
}45
}