+977-9849845061

How to backup all databases in SQL Server

When we have more databases on a Server to backup, it is very time to consume to backup all database manually. In such case, we can use T-SQL to automate these tasks. We can use SQL Server Management Studio to back up the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Using T-SQL we can generate backup commands. With the use of cursors, we can cursor through all of the databases to backup them one by one.

 

The above T-SQL will backup each database within the instance of SQL Server.

NoteĀ @path to be replaced by the backup directory. In my case backup directory isĀ ‘C:\SQL_Server_Backup\’


File Naming Format

DBname_YYYYDDMM.BAK


DBname_YYYYDDMM_HHMMSS.BAK

 

 

Screenshot:

How to backup all databases in SQL Server
How to backup all databases in SQL Server

 

All database backed up with single T-SQL script.

This Script will allow you to backup all dbs in the fly. the script looks for .mdf file in your sql instance and back all of the files it find except the system databases...
This Script will allow you to backup all dbs in the fly. the script looks for .mdf file in your SQL instance and back all of the files it finds except the system databases…
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...