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
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.
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 !! 🙂
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…