SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

SQL Backup Script for All Databases in SQL Server Instance

Undocumented stored procedure sp_Msforeachdb can help SQL administrators and t-sql developers to repeat tasks on each of sql databases running on the SQL Server instance.
Taking sql backup of all sql databases on a SQL Server can be considered as a repeatitive task for many administrators.
So it is certain that an SQL Server backup script using sp_Msforeachdb undocumented stored procedure will make it sql backups easier for many sql professionals.

EXEC sp_Msforeachdb "BACKUP DATABASE [SQL Database] TO DISK = 'D:\?20100707.bak'"

Before dealing more on how sp_Msforeachdb can be used to create a sql backup script for taking backups of all sql databases, lets concantrate on t-sql BACKUP DATABASE command.

CREATE PROC BackupDatabase (
  @DatabaseName sysname ,
  @SQLBackupFolder nvarchar(400)

DECLARE @BackupSQLScript nvarchar(max)
DECLARE @SQLBackupFileName nvarchar(400)

SET @SQLBackupFileName =
  REPLACE(@DatabaseName,' ','-') +

SET @BackupSQLScript = '
BACKUP DATABASE [' + @DatabaseName + ']
TO DISK = ''' + @SQLBackupFolder + @SQLBackupFileName + ''''

print @BackupSQLScript
EXEC sp_executesql @BackupSQLScript

Here is how you can use the above sql backup procedure to backup database in your SQL Server.

DECLARE @DatabaseName sysname
DECLARE @SQLBackupFolder nvarchar(400)
SET @DatabaseName = 'SQL Database'
SET @SQLBackupFolder = 'D:\'
EXEC BackupDatabase @DatabaseName, @SQLBackupFolder

MS SQL Backup Script using sp_Msforeachdb

And by using undocumented stored procedure sp_Msforeachdb, sql developers or SQL Server database administrators can take sql backup of all databases on the target SQL Server instance by using the following code.

EXEC sp_Msforeachdb "
DECLARE @BackupFolder nvarchar(400);
SELECT @BackupFolder = 'D:\';
EXEC BackupDatabase '?', @BackupFolder

If you have separate folders for your sql server databases, you can use the below sql backup script too.

EXEC sp_Msforeachdb "
DECLARE @BackupFolder nvarchar(400);
SELECT @BackupFolder = 'D:\' + '?' + '\';
EXEC BackupDatabase '?', @BackupFolder

If you are a SQL Database Administrator or T-SQL developer who prefer to use graphical user interfaces (GUI), you can use Microsoft SQL Server Management Studio SQL Server Backup Database Wizard as SQL Backup Tool.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.