Jump to content

How to take backup of all SQL Databases automatically ? scheduled SQL backup.


Recommended Posts

How to Take backup of All of the Databases available on instance ? Sometimes Its required to take all of the Databases to be backed up periodically for more reliable backup process. If You require to get backup of any critical server's Database then You shouldn't do manually always, It will be better to automate the Process.

SQL Query to take backup for All the Databases:- 

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

-- specify database backup directory
SET @path = 'D:\BKP\'  
 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

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
 

copy the above query and save it with file name i.e- sqlbackupquery. The above query will give the Database backed up file (.bak) on the location You will set.

You can run it over SQL server Management Studio (SSMS) or on sqlcmd.

Query will give you the database backup in the described format below.
DBName_YYYMMDD.BAK

You can run it from SQL CMD, with the required parameters. for Example see below.

sqlcmd -S .\InstanceName -i C:\SQLBackup\SQLBackupQuery.sql (backup query file location and file name)

If you require it to schedule the Process for backup of all SQL databases on the path where you want You can schedule it from Task Scheduler.

To create a task go to Run (windows +R) - taskschd.msc and create a task.  schedule it as per the requirement.
 

Createtask1.PNG



Here define the action.
 

create%2Btask2.JPG



Note- User should have the Admin right,Logon as batch Job Right. You also can automate on SQL Server Express from above query and Process.

Enjoy, Now You will be able to take the databases backup in one go and schedule it as per your requirement for convenience and saving the time.

Thanks,
Sanjeev

PS- Please write to me or comment if you have any question/Feedback.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Privacy Policy