:::: MENU ::::

Backup all SQL Server databases (User and system) through T-SQL

  • Nov 12 / 2008
  • 0
Backup and Restore, dbDigger, System Stored Procedures, T-SQL Scripts

Backup all SQL Server databases (User and system) through T-SQL

There may be some situation where you are in need to backup all SQL Server databases. If you have a number of SQL Server databases (in majority of cases) then it would be a very good idea to use T-SQL command that will backup all SQL Server databases. Do not forget to calculate available space in drive which you are going to provide as backup destination. Use following T-SQL command to backup all SQL Server databases through a single command

SP_MSforeachdb
'BACKUP DATABASE ? TO DISK = ''C:backup?.bak'' WITH INIT'

We have used system stored procedure SP_MSforeachdb to invoke the given command for all databases. T-SQL command for SQL Server backup is provided next to SP_MSforeachdb as parameter. ? in the above command specifies the database name which is going to be handled by the stored procedure SP_MSforeachdb. It will provide the name of databases automatically one by one. It is important to note that above command will backup all system databases along with user databases

Consult us to explore the Databases. Contact us