SQL full database BACKUP through SQL SCRIPT

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 4572 red flag

This article explains how we can take SQL database backup through SQL Scripts.

 Download source code for SQL full database BACKUP through SQL SCRIPT


Database backup is very important for any Company, this article explains how we can take SQL backup via SQL script. SQL script help us to take database backup in one-shot.We also take multiple database backup through Script.     

Description with Script

It is very easy to use, In below query we used cursor to select multiple database name one by one. And we add date time with database file name then we can easily find updated backup from given path.

DECLARE @DBname VARCHAR(50) -- DB database name  
DECLARE @DBpath VARCHAR(256) -- DB backup files path  
DECLARE @DBfileName VARCHAR(256) -- DB filename for backup  
DECLARE @DBfileDate VARCHAR(20) --  ADD datetime with filename
-- set database backup path
SET @DBpath = 'C:\Backup\'  
-- Set file name with datetime
Select @DBfileDate=CONVERT(varchar(20),GETDATE(),112) + REPLACE(CONVERT(varchar(5),GETDATE(),108),':','')
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name  IN ('data','Test')  -- select database name for backup
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @DBname    
   SET @DBfileName = @DBpath + @DBname + '_' + @DBfileDate + '.BAK'  
   BACKUP DATABASE @DBname TO DISK = @DBfileName  
  FETCH NEXT FROM db_cursor INTO @DBname    
CLOSE db_cursor   
DEALLOCATE db_cursor
Query Screen shot

Output of Script

After execution of script output as below:-


  • It is very easy to use.
  • We can take multiple database backup from one script.
  • Easy to understood.
  • we can easily schedule this script for database backup as per your need like weekly or monthly.
Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Sandeep Raturi
Member Level: Starter
Member Status: Member
Member Since: 2/28/2012 10:43:45 AM
Country: India
Regards Sandeep Raturi(Systems Analyst) Email: s.raturi89@gmail.com
Systems Analyst

Login to vote for this post.

Comments or Responses

Posted by: Robbieweeks on: 2/23/2018 | Points: 25
Thanks a lot for that tips)

Login to post response

Comment using Facebook(Author doesn't get notification)