Backup Databases on Remote Server

As the last in a three-post series on database maintenance stored procedures (the other two being CHECKDB and file clean-up), the following stored procedure extends a T-SQL script we discussed previously that backs-up all databases on a server. The procedure below adds the capability of specifying the backup type as either FULL or LOG, as well as specifying the backup directory and, optionally, a remote server on which to run the script.

This procedure makes use of linked server functionality, so be aware of security contexts and credentials. Specifically, ensure the account under which this procedure executes has the necessary permissions on the target server on which the backups will be performed, as well as write permissions to the file path to which the backup files will be saved.

We’ll create the procedure within our maintenance schema (mx).

CREATE PROCEDURE mx.uspBackup
    @strBackupType VARCHAR(4) = N'FULL' -- options: FULL, LOG
    , @strBackupDirectory VARCHAR(50) -- the local file path
    , @sysServerName SYSNAME = NULL -- optional remote server name
AS
BEGIN
    -- Create a linked server, if needed
    IF @sysServerName IS NOT NULL
    BEGIN
        -- Create an effectively-unique name for a linked server
        DECLARE @sysServer SYSNAME = @sysServerName
            + N'_'
            + CONVERT(CHAR(36), NEWID())
        ;

        -- If the linked server already exists, drop it
        IF EXISTS (SELECT name FROM sys.servers WHERE server_id <> 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;

        -- Add the linked server
        EXECUTE master.dbo.sp_addlinkedserver
            @server = @sysServer
            , @srvproduct = N'SQL Server'
        ;

        -- Add login information to the linked server
        EXECUTE master.dbo.sp_addlinkedsrvlogin
            @rmtsrvname = @sysServer
            , @useself = N'True'
            , @locallogin = NULL
            , @rmtuser = NULL
            , @rmtpassword = NULL
        ;
    END

    -- Create the file directory
    EXECUTE master.dbo.xp_create_subdir@strBackupDirectory;

    -- Declare and initialize the backup SQL
    -- Exclude tempdb from all backup operations
    DECLARE @strSQL NVARCHAR(MAX) = N'
        IF ''?'' <> ''tempdb'' -- Do not execute against tempdb'
    ;

    -- If performing a log backup, only include simple recovery model dbs
    IF @strBackupType = N'LOG'
        SET @strSQL += N'
AND N''SIMPLE'' <> ( -- Confirm recovery model for log backups
      SELECT recovery_model_desc
      FROM sys.databases
      WHERE name = N''?'')'
        ;

    -- Define the backup path
    SET @strSQL += N'
BEGIN
      -- Define the backup path
      DECLARE @strPathFull NVARCHAR(128) =
            N''' + @strBackupDirectory + '\?\?''
            + N''_backup_''
            + CONVERT(CHAR(8), GETDATE(), 112)
            + N''_''
            + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 114), '':'', '''')'
    ;

    -- Add the backup type file extension and backuyp type command
    IF @strBackupType = N'FULL'
        SET @strSQL += N'
            + N''.bak''
;
      -- Backup the database
      BACKUP DATABASE [?]'
        ;
    ELSE IF @strBackupType = N'LOG'
        SET @strSQL += '
            + N''.trn''
      ;
      -- Backup the log
      BACKUP LOG [?]'
        ;

    -- Continue the backup T-SQL command
    SET @strSQL += N'
      TO DISK = @strPathFull
      WITH
            NOFORMAT
            , NOINIT'
    ;

    -- Set the backup name based on the backup type requested
    IF @strBackupType = N'FULL'
        SET @strSQL += N'
            , NAME = N''?-Full Database Backup'''
;
    ELSE IF @strBackupType = N'LOG'
        SET @strSQL += N'
            , NAME = N''?-Log Backup'''
        ;

    -- Continue the backup T-SQL command
    SET @strSQL += N'
            , SKIP
            , NOREWIND
            , NOUNLOAD
            , STATS = 10
      ;
     
      -- Declare a variable to retrieve backup position
      DECLARE @intPos INT;
     
      -- Initialize the backup position variable
      SELECT @intPos = position
      FROM msdb.dbo.backupset
      WHERE
            database_name = N''?''
            AND backup_set_id = (
                  SELECT MAX(backup_set_id)
                  FROM msdb.dbo.backupset
                  WHERE database_name = N''?'')
      ;
     
      -- Check for error in initializing the backup position variable
      IF @intPos IS NULL
            RAISERROR(N''Backup verification failed for database ''''?''''.'', 16, 1)
      ;
     
      -- Verify the backup was created successfully
      RESTORE VERIFYONLY
      FROM DISK = @strPathFull
      WITH
            FILE = @intPos
            , NOUNLOAD
            , NOREWIND
      ;
END'
    ;

    -- Execute the SQL against each database, remote if needed
    IF @sysServerName IS NULL
        -- Execute locally
        EXECUTE sp_MSforeachdb@strSQL;
    ELSE
    BEGIN
        -- Prepare SQL for remote execution
        DECLARE @strSQL2 NVARCHAR(MAX) =
            N'EXECUTE (N''EXECUTE sp_MSforeachdb N'''''
            + REPLACE(@strSQL, N'''', N'''''''''')
            + N''''''') AT '
            + N'[' + @sysServer + N']'
        ;

        -- Execute remotely
        EXECUTE sp_executesql@strSQL2;

        -- Drop the linked server
        IF EXISTS(SELECT name FROM sys.servers WHERE server_id <> 0 AND name = @sysServer)
            EXECUTE master.dbo.sp_dropserver
                @server = @sysServer
                , @droplogins = N'droplogins'
            ;
    END
END

Advertisements
Post a comment or leave a trackback: Trackback URL.

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

%d bloggers like this: