Back Up All Databases Using T-SQL

Introduction

All of us have a daily routine to create a back up of databases on a daily or weekly basis. I am here presenting a simple script that can create backup of all databases except the system databases. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak, where the name of database will be appended with date at which back up is created in format NameOfDatabase_YYYYMMDDHHMMSS where YYYY is Year, MM is month in numeric, DD Date, HHMMSS is hours, minutes and seconds.

1. Backup all databases except the system databases
2. Backup files will be saved in the format NameOfDatabase_YYYYMMDDHHMMSS.bak

-Name of database
DECLARE @DatabaseName VARCHAR(50)

-Path of backup folder
DECLARE @BackupPath VARCHAR(256)

-Name of backup file
DECLARE @BackUpFileName VARCHAR(256)

-Get current date used for suffixing with file name
DECLARE @Date VARCHAR(20)

-Specify path for database backup directory. 
-Make sure directory exists before executing script, else script will give error
SET @BackupPath = ‘C:\Backup\’

-Get current date used for suffixing with file name
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),‘:’,")

-Declaring cursor for storing database names
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases

-Excluding system databases
WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

-For specific database, replace TestDB with 
-required database name in the below line and uncomment it: 
AND name IN (‘TestDB’)

-Fetching database names from cursor to local variable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

-Loop for all databases
WHILE @@FETCH_STATUS = 0
BEGIN

    -Setting file name in format NameOfDatabase_YYYYMMDDHHMMSS
    SET @BackUpFileName = @BackupPath + @DatabaseName + ‘_’ + @Date + ‘.bak’

    -Creating back up
    BACKUP DATABASE @DatabaseName TO DISK = @BackUpFileName

    -Fetching next database name
    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

-Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor