Create database backup using SQL query in SQL SERVER


Taking database backup is a day to day operation which database administrators/developers perform. Below is a script for taking database backup in sql server.

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 

SET @path = 'C:\DB_Backup'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DATABASE_NAME')  

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

The above script will create a database backup(.bak) file in C:\DB_Backup.

You can take multiple database backup by adding database names separated by comma in the above script.

SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('DATABASE_NAME_1, DATABASE_NAME_2, DATABASE_NAME_3')

 

 

Tags: , ,

Comments & Responses

Leave a Reply

Your email address will not be published. Required fields are marked *

*


+ 7 = 10

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>