May 26, 2022

Samir Daoudi's Technical Blog

SharePoint, Office 365, C#, .Net, SQL Server News, Tips, Ebooks, Events, Webinars ….etc

Backup All databases using T-SQL

In this article, I will share a nice handy script I found while trying to find a solution to the problem of working on different machines accross the day.

Most of my source codes are synchronized to Azure DevOps or One Drive (more personal work), however, I had no clean solution to backup all my databases in one go.

In the past, I developed an SSIS Package to achieve this, however, I had to install SSIS in evey laptop/desktop I used, which was a bit heavy.

Here is a clean T-SQL code that you can run any time which will take a backup of all databases (excluding master, tempdb…etc.)

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\Backup\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor

Leave a comment

You may have missed

Show Buttons
Hide Buttons

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

%d bloggers like this: