Database backup is a must know task for a DBA. In this blog we’ll look at T-SQL script to backup all databases at once.

The logic is to iterate through all the online/active user databases and construct dynamic backup query. The query provides option to specify the backup folder and the backup options. The complete backup path is made up off @backuppath  + @database + timestamp.   The T-SQL for the same is given below

The cursor backup_cursor iterates through the online user databases and constructs dynamic backup query by concatenating backup command, database,@ fullbackuppath(@backuppath+@database) and the @backupoptions.

The above query can be scheduled as a SQL Server Agent job to automatically take backup as per the backup strategy.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook