Create backup devices for all databases via T-SQL

Dating back to the formative days of SQL Server, backups were referred to as dumps.  Thus the stored procedure is sp_addumpdevice, even though when you back up a database you use the Backup command.  Whatever.

I have always enjoyed looking at the information stored in the metadata in SQL Server, and one such source is sys.databases, a repository of all databases defined on the server instance.   By appending strings around the name, and filtering for database_id > 4, it’s easy to build dump devices for each database.   I use one dump device to back up the system databases: if you want to have an individual dump device for each system DB, then remove the WHERE clause.

--CreateDumpDevicesFromSysdatabases.sql
/*
 Creates backup devices for all databases.
 The code from Tim Ford reads the registry to see where backups are stored, then used to create dump devices.
 Run Part 1 to create the function, then run Part 2 to create the dump devices.
 Change to text output (Ctrl-T), then run script. Copy the output to a new query window and run it.
 Found a link to Tim Ford's code at https://www.mssqltips.com/sqlservertip/1966/function-to-return-default-sql-server-backup-folder/,
 then found the actual code at http://thesqlagentman.com/2010/04/turn-management-studio-into-a-web-browser/.

 WW, 17 Oct 07
 WW, 16 May 15
 WW, 12 May 16: Added Tim Ford's registry reader code
*/

--****************************************************--
--Code for fn_SQLServerBackupDir():
--Author: Timothy Ford (sqlagentman@yahoo.com)
-- returns nvarchar(4000) = dbo.fn_SQLServerBackupDir()
--****************************************************--
--PART 1:
USE master;

IF OBJECT_ID('dbo.fn_SQLServerBackupDir') IS NOT NULL
 DROP FUNCTION dbo.fn_SQLServerBackupDir
GO

CREATE FUNCTION dbo.fn_SQLServerBackupDir()
RETURNS NVARCHAR(4000)
AS
BEGIN
   DECLARE @path NVARCHAR(4000)

   EXEC master.dbo.xp_instance_regread
       N'HKEY_LOCAL_MACHINE',
       N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
       @path OUTPUT,
       'no_output'
   RETURN @path
END;

--SELECT fn_SQLServerBackupDir = dbo.fn_SQLServerBackupDir();


--PART 2: SET OUPUT TO TEXT! CTRL-T!
DECLARE @BackupLocation NVARCHAR(4000);
SELECT @BackupLocation = master.dbo.fn_SQLServerBackupDir() + '\';
SET NOCOUNT ON;

SELECT 'USE master;';

--Each DB gets two dump devices: one for data, one for log.
SELECT 'EXEC sp_addumpdevice ''disk'', ''' + name + '_data_bkup'', ''' 
    + @BackupLocation + name + '_data_bkup.bak''' + ';'
    + CHAR(13) + CHAR(10)
    + 'EXEC sp_addumpdevice ''disk'', ''' + name + '_log_bkup'', ''' 
    + @BackupLocation + name + '_log_bkup.bak''' + ';'
FROM master.sys.databases
WHERE database_id > 4
UNION ALL
--I back up all system databases to the same file via append.
SELECT 'EXEC sp_addumpdevice ''disk'', ''SystemDB_data_bkup'', ''' 
    + @BackupLocation + 'SystemDB_data_bkup.bak''' + ';'
    + CHAR(13) + CHAR(10)
    + 'EXEC sp_addumpdevice ''disk'', ''SystemDB_log_bkup'', ''' 
    + @BackupLocation + 'System_log_bkup.bak''' + ';';

SET NOCOUNT OFF;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s