How to Avoid Running Your SQL Server Script on the Wrong Server

You might be a database administrator or a SQL Server developer; running queries through SQL Server Management studio is a routine task. Though you might connect to multiple servers at a time and work on multiple activities, there are chances that you might execute a script on a wrong server. Do not worry, this article will help you to avoid executing scripts on a wrong server

Notice the alert

Run this script on your server and the next time you hit the “New Query” button on the SQL Server management studio, you will see a new query window with a commented line carrying an alert message “please check if you are on correct server and then execute the script”. Now this will help you to verify the server name before executing the script. In addition to this message, whatever you type in the new query window will be commented. This ensures that even by accident you will not execute a wrong query on a wrong server. Of course, any time you can remove the first line and you are ready to execute the script.

DECLARE @f TABLE (
    ID INT IDENTITY
    ,cfname VARCHAR(8000)
    )

INSERT INTO @f
EXECUTE xp_cmdshell 'dir "C:\Program Files (x86)\Microsoft SQL Server\" /s /b'

DECLARE @orig VARCHAR(8000)

SET @orig = (
        SELECT TOP 1 cfname
        FROM @f
        WHERE cfname LIKE '%sqlfile.sql'
        )

DECLARE @cmd VARCHAR(8000)

SET @cmd = 'ren "' + @orig + '" "SQLFile_orig.sql"'

PRINT @cmd

EXEC xp_cmdshell @cmd

DECLARE @intfs INT
DECLARE @intts INT
DECLARE @inteo INT
DECLARE @msg VARCHAR(1000)
DECLARE @strcmd VARCHAR(1000)
DECLARE @newvariable INT
DECLARE @fapath VARCHAR(2080)
DECLARE @cstring VARCHAR(max)
DECLARE @cpath VARCHAR(1000)
DECLARE @cfname VARCHAR(1000)

SET @cpath = replace(@orig, '\SQLFile.sql', '')

SELECT @cpath

SET @cfname = 'SQLFile.sql'
SET @cstring = '/* PLEASE CHECK IF YOU ARE ON CORRECT SERVER AND THEN EXECUTE THE SCRIPT'

SELECT @msg = 'Script is running....'

EXECUTE @newvariable = sp_OACreate 'Scripting.FileSystemObject'
    ,@intfs OUT

SELECT @fapath = @cpath + '\' + @cfname

SELECT @fapath

IF @newvariable = 0
    SELECT @inteo = @intfs
        ,@msg = 'Script is running... "' + @fapath + '"'

IF @newvariable = 0
    EXECUTE @newvariable = sp_OAMethod @intfs
        ,'CreateTextFile'
        ,@intts OUT
        ,@fapath
        ,2
        ,True

IF @newvariable = 0
    SELECT @inteo = @intts
        ,@msg = 'Script is running... "' + @fapath + '"'

IF @newvariable = 0
    EXECUTE @newvariable = sp_OAMethod @intts
        ,'Write'
        ,NULL
        ,@cstring

IF @newvariable = 0
    SELECT @inteo = @intts
        ,@msg = 'Script is running... "' + @fapath + '"'

IF @newvariable = 0
    EXECUTE @newvariable = sp_OAMethod @intts
        ,'Close'

IF @newvariable <> 0
BEGIN
    DECLARE @stringsource VARCHAR(255)
    DECLARE @stringdescr VARCHAR(255)
    DECLARE @stringhelp VARCHAR(255)
    DECLARE @inthelp INT

    EXECUTE sp_OAGetErrorInfo @inteo
        ,@stringsource OUTPUT
        ,@stringdescr OUTPUT
        ,@stringhelp OUTPUT
        ,@inthelp OUTPUT

    SELECT @msg = 'Error whilst ' + coalesce(@msg, 'doing something') + ', ' + coalesce(@stringdescr, '')

    RAISERROR (
            @msg
            ,16
            ,1
            )
END

EXECUTE sp_OADestroy @intts

EXECUTE sp_OADestroy @intfs

Script to remove the alert:

If you feel that you have acquired the habit of verifying the server name before executing queries and if you need to remove this commented alert from the query window, execute this script and the next time you hit the “New query” button, the query window will be blank.

DECLARE @files TABLE (
    ID INT IDENTITY
    ,FileName VARCHAR(8000)
    )

INSERT INTO @files
EXECUTE xp_cmdshell 'dir "C:\Program Files (x86)\Microsoft SQL Server\" /s /b'

DECLARE @orig VARCHAR(8000)

SET @orig = (
        SELECT TOP 1 FileName
        FROM @files
        WHERE FileName LIKE '%sqlfile_orig.sql'
        )

DECLARE @rep VARCHAR(1000)

SET @rep = replace(@orig, 'sqlfile_orig.sql', 'sqlfile.sql')

DECLARE @cmd VARCHAR(8000)

SET @cmd = 'del "' + @rep + '"'

SELECT @cmd

EXEC xp_cmdshell @cmd

SET @cmd = 'ren "' + @orig + '" "SQLFile.sql"'

SELECT @cmd

EXEC xp_cmdshell @cmd

Database recovery tool:

If you have run your script on a wrong server and corrupted SQL Server then you can use database recovery tools such as DataNumen SQL Recovery.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst data corruption and excel recovery software products. For more information visit www.datanumen.com