SQL Script to backup all databases

The below query sql server backup single “Test” databases

DECLARE @DBNAME VARCHAR(50) -- DATABASE NAME
DECLARE @PATH VARCHAR(256)  -- BACKUP FILES PATH
DECLARE @FILENAME VARCHAR(256) -- FILENAME FOR BACKUP
DECLARE @FILEDATE VARCHAR(20) -- USED FOR FILE NAME
 
-- DATABASE BACKUP FILE DIRECTORY PATH AND FOLDER
SET @PATH = 'E:\Shared\DatabaseBackup\BACKUP-FILE-DB\'
 
--FILE NAME WITH DATE AND TIME
SELECT @FILEDATE = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
 
DECLARE DB_CURSOR CURSOR FOR  
SELECT NAME FROM MASTER.DBO.SYSDATABASES 
WHERE NAME IN ('Test')  -- INCLUDE THESE DATABASES
    --WHERE NAME IN ('DATABASE-1','DATABASE-2','DATABASE-3','DATABASE-4','DATABASE-N')  -- INCLUDE THESE DATABASES
 
OPEN DB_CURSOR   
    FETCH NEXT FROM DB_CURSOR INTO @DBNAME
     WHILE @@FETCH_STATUS = 0   
        BEGIN   
          SET @FILENAME = @PATH + @DBNAME + '_' + @FILEDATE + '.BAK'  
          BACKUP DATABASE @DBNAME TO DISK = @FILENAME  
            FETCH NEXT FROM DB_CURSOR INTO @DBNAME   
        END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

For Multiple databases backup query looks like

DECLARE @DBNAME VARCHAR(50) -- DATABASE NAME
DECLARE @PATH VARCHAR(256)  -- BACKUP FILES PATH
DECLARE @FILENAME VARCHAR(256) -- FILENAME FOR BACKUP
DECLARE @FILEDATE VARCHAR(20) -- USED FOR FILE NAME
 
-- DATABASE BACKUP FILE DIRECTORY PATH AND FOLDER
SET @PATH = 'E:\Shared\DatabaseBackup\BACKUP-FILE-DB\'
 
--FILE NAME WITH DATE AND TIME
SELECT @FILEDATE = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
 
DECLARE DB_CURSOR CURSOR FOR  
SELECT NAME FROM MASTER.DBO.SYSDATABASES 
     WHERE NAME IN ('DATABASE-1','DATABASE-2','DATABASE-3','DATABASE-4','DATABASE-N')  -- INCLUDE THESE DATABASES
 
OPEN DB_CURSOR   
    FETCH NEXT FROM DB_CURSOR INTO @DBNAME
     WHILE @@FETCH_STATUS = 0   
        BEGIN   
          SET @FILENAME = @PATH + @DBNAME + '_' + @FILEDATE + '.BAK'  
          BACKUP DATABASE @DBNAME TO DISK = @FILENAME  
            FETCH NEXT FROM DB_CURSOR INTO @DBNAME   
        END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR

The output for single database backup looks like,

Pin it

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2