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
Leave a Reply