3rd highest salary in sql server

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,

ANIL SINGH

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Google Amazon and Flipkart Book Store!

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.
ASK Questions
SQL Server NodeJs TypeScript JavaScript Angular SQL Server My Book