Automation of taking backup of all databases within single SQL Server instance

ByDr. SubraMANI Paramasivam

Automation of taking backup of all databases within single SQL Server instance

Below scripts will enable you to take backup of every single database except Model & TempDB databases, by using cursors on it. Strongle advise not to run this with very large databases or in OLTP environments where the systems are heavily used.

 

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
‘F:\’

DECLARE All_Backup_Cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN (‘Model’,’TempDB’);
OPEN All_Backup_Cursor
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
END
CLOSE All_Backup_Cursor
DEALLOCATE All_Backup_Cursor

About the Author

Dr. SubraMANI Paramasivam administrator

Dr.SubraMANI Paramasivam is a Data Platform MVP, Power BI Group Leader, International Speaker, Microsoft Certified Trainer and Principal Solutions Architect at DAGEOP Ltd, having 20+ years of DB, BI & Analytics experience. Dr.SubraMANI Paramasivam is a frequent speaker in SQLBITS, PASS Summit, PASS SQL Saturdays, SQLRelay, Data Platform Summit, MCT Summit, SQLServerGeeks Summit, INSIDESQL UK, Data Awareness Programmes, Data Day Events. He likes to enlighten the education & data and started his own journey with FREE Data Awareness Programme (www.dataap.org), in remote areas who are in need. Mani speaks a lot about Visualization, Data Science, R Analytics, Azure concepts, Performance Tuning, DB & SQL Server Architecture, DBA, Partitioning, Resource Governor, Disaster Recovery, High Availability, Development, T-SQL Programming and Microsoft certifications.

Comments Are Closed!!!