How to get script of backup and restore of multiple databases
Scenarios
Created a new SQL Server instance and would like to backup/restore all databases from an old SQL Server. The number of databases are approx 99+. Manually backup and restore each database with SSMS is a time-consuming activity. Let’s try to make backup scripts of all databases and also a restore script of multiple databases.
Get the script of backup and restore of multiple databases
Backup Script
Execute the below script on SQL Server. Change the backup folder location as per your requirement.
|
1 2 3 4 5 |
DECLARE @folderpath VARCHAR (1000) SELECT @folderpath = 'D:Backup' -- Backup Location SELECT 'BACKUP DATABASE['+NAME+'] TO DISK = ''' +@folderpath+name+'.bak'' WITH COMPRESSION,STATS = 10' FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','distribution') |
As a result, we will get a script. Now we need to execute the same on the same SQL Server to perform the backup process.
Restore Script
Now execute the below script on SQL Server, remember we have to use the backup folder location in the script.
|
1 2 3 4 5 6 |
DECLARE @folderpath VARCHAR (1000) SELECT @folderpath = 'D:Backup' -- Backup Location SELECT 'RESTORE DATABASE['+NAME+'] FROM DISK = ''' +@folderpath+name+'.bak'' WITH NORECOVERY, REPLACE, STATS = 5' FROM master.sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','distribution') |
Enjoy !! 🙂