SQL full database BACKUP through SQL SCRIPT

Sandeepraturi
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 923 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

Introduction

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),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
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    
WHILE @@FETCH_STATUS = 0   
BEGIN 
   SET @DBfileName = @DBpath + @DBname + '_' + @DBfileDate + '.BAK'  
   BACKUP DATABASE @DBname TO DISK = @DBfileName  
  FETCH NEXT FROM db_cursor INTO @DBname    
END   
CLOSE db_cursor   
DEALLOCATE db_cursor
Query Screen shot



Output of Script

After execution of script output as below:-


Conclusion

  • 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

Sandeepraturi
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
http://www.dotnetfunda.com/profile/sandeepraturi.aspx
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)

Login to post response

Comment using Facebook(Author doesn't get notification)