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_cursorQuery 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.
About the Author
