September 28, 2022

Samir Daoudi's Technical Blog

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

Backup all your databases with TSQL

Hi guys, today, I had really enough of backing up my databases due to lots of VM snapshots and restore.

I did create in the past an SSIS package, but when you are in hurry, you just want things done quickly.

So, the following script will check for all the databases (excluding the system ones). For each database, the script will back it up to c:\backup\ using the following name DBNAME_Date.bak.

Enjoy 🙂

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 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   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  
   FETCH NEXT FROM db_cursor INTO @name   

CLOSE db_cursor   
DEALLOCATE db_cursor



If you want to go an extra mile, you can call this script from a .bat file and make sure this runs before every shut down.

PS: I personally backup my databases to a OneDrive location every week.

Have a nice day.

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: