SQL full database BACKUP through SQL SCRIPT

Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 2260 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
Thanks & Regards Sandeep Raturi(Sr. software engineer) Email: s.raturi89@gmail.com
Senior Software Engineer

Login to vote for this post.

Comments or Responses

Posted by: Robbieweeks on: 2/23/2018 | Points: 25
Thanks a lot for that tips)
Posted by: Rahulsinghdigit on: 8/4/2018 | Points: 25
My brother suggested I might like this website. He was entirely right. This post actually made my day. You cannot imagine simply how much time I had spent for this info! Thanks! http://www.axivasichem.com/popular-syring-filter.aspx
Posted by: Sandeepraturi on: 8/4/2018 | Points: 25
thanks rahul for appericiation.
Posted by: Rajeshsharma on: 8/13/2018 | Points: 25
Hi, your blog is truly faultless and unique. Very wonderful your article, this is best article. I read really perfect your article more information one of other blog zone. http://www.prathimahospitals.com/department/neurology/

Posted by: Sandeepraturi on: 8/13/2018 | Points: 25
Thanks rajesh for appericiation.
Posted by: Vikasdotnet on: 1/9/2019 | Points: 25
Hi, It very nice content and uniqe feature. Thanks for your hard and dedicated work . Keep It up.I read really perfect your article more information one of other blog zone. http://www.dotnetbasic.com
Posted by: Morinmote1 on: 3/16/2019 | Points: 25

Login to post response

Comment using Facebook(Author doesn't get notification)