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.
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.
Execute the below script on source SQL server. Change the backup folder location as per your requirement.
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')
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.
Now we need to execute the Result 1 on the same source SQL Server to perform the backup process.
Backup file generated at the provided location
Now execute the below Query 2 script on source SQL Server, remember we have to use the backup folder location in the script.
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')
Now we need to execute the Result 2 on the target SQL Server to perform the actual restore activity.
Enjoy !! 🙂