CheckDB on Remote Server

While we are on the subject of maintenance scripts (see previous post on file clean-up), here is one to run a CHECKDB against all databases, even on a remote server. We’ll make use of the sp_MSforeachdb system stored procedure as well as linked servers.

Generally linked servers are unpopular as they can present a security risk due to embedded credentials; however, we will minimize that risk by specifying that credentials are simply passed through to the linked server rather than embedding alternate credentials within the linked server definition. Since we are passing through credentials, you should ensure that the credentials under which the CHECKDB procedure will execute are sufficient to access and run the commands on the linked server.

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

-- Create a stored procedure in our maintenance schema
CREATE PROCEDURE mx.uspCheckDB
    @sysServerName SYSNAME = NULL -- server name if to be executed remotely
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 (however unlikely), 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 = @sysServerName
            , @useself = N'True'
            , @locallogin = NULL
            , @rmtuser = NULL
            , @rmtpassword = NULL
        ;
    END
    -- Initialize variables
    DECLARE @strSQL NVARCHAR(MAX) = N'-- Set the database context
USE [?];
-- Run the CHECKDB command
DBCC CHECKDB(N''?'') WITH NO_INFOMSGS;'
    ;
    -- 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
        -- Wrap the existing SQL within an EXECUTE command and specify
        -- to execute that command at the linked server; also need to
        -- replace single quotes with quadruple quotes as the command
        -- will execute at the third nested level
        DECLARE @strSQL2 NVARCHAR(MAX) =
            N'EXECUTE (N''EXECUTE sp_MSforeachdb '''''
            + REPLACE(@strSQL, '''', '''''''''')
            + 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: