Skip to main content
Our Tech Ideas

Backup & Restore Multiple Databases

Backup and restore all databases from an old SQL server and restore it on new SQL server. The number of databases are approx 99+. Let’s try to make backup and restore scripts of all databases.

Note: Here backup and restore server are defferent. Before executing restore script on target server, default path for restoration must be set in the target server.

Solution

By using Query 1 we can generate the backup script of all database from source server. Then we need to copy the result of the Query 1 and run the same on the source server to perform the actual backup activity. Once backups are ready, then we need to generate the restore script. To generate the restoration script we need to execute the Query 2 on the source server. Once the restoration script is ready, further we can execute the final restoration query on target server.

Backup Script

Execute the below script on source SQL server. Change the backup folder location as per your requirement.

Query 1

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = 'D:\New folder\' -- 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')

Steps

To run a script on a Microsoft SQL Server using SSMS.

  • Open Microsoft SQL Server Management Studio
  • Select [New Query] from the toolbar
  • Copy the ‘Example Query’
  • Select the database to run the query against
  • Paste the ‘Example Query’ into the query window
  • Click the [Execute] button

As a result, we will get a script.

Result 1

Now we need to execute the Result 1 on the same source SQL Server to perform the backup process.

Result 1.1

Backup file generated at the provided location

Result 1.2

Restore Script

Now execute the below Query 2 script on source SQL Server, remember we have to use the backup folder location in the script.

Query 2

DECLARE @folderpath VARCHAR (1000)
SELECT @folderpath = 'D:\New folder\' -- Backup Location
SELECT 'RESTORE DATABASE ['+NAME+'] FROM DISK = ''' +@folderpath+name+'.bak'' 
WITH RECOVERY, REPLACE, STATS = 5' -- Change RECOVERY or NORECOVERY
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')

 Result 2

Now we need to execute the Result 2 on the target SQL Server to perform the actual restore activity.

Enjoy !! 🙂