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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems