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)
)
AS
DECLARE @BackupSQLScript nvarchar(max)
DECLARE @SQLBackupFileName nvarchar(400)
SET @SQLBackupFileName =
REPLACE(@DatabaseName,' ','-') +
CONVERT(VARCHAR,GETDATE(),112) + '.bak'
SET @BackupSQLScript = '
BACKUP DATABASE [' + @DatabaseName + ']
TO DISK = ''' + @SQLBackupFolder + @SQLBackupFileName + ''''
print @BackupSQLScript
EXEC sp_executesql @BackupSQLScript
GO
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.