April 2005

You are currently browsing the monthly archive for April 2005.

You can store databases BACKUP’s encrypted. It is not necessary for this purpose any additional programs. Windows 2K/2k3/XP and SQL Server suffices. It is possible to crypt BACKUP with EFS. For this purpose, it is necessary to encrypt folder where SQL Server makes BACKUP. The certificate of a encryption agent needs to be saved. It establish on a computer where is restored BACKUP. What to write down the encrypted copy on a tape, it is necessary to use standard program – NTBACKUP. Below procedure which automates this job.


– $CARDS: mssql/tools/task_NTBackup.sql,v 1.1 2004/08/06 18:04:46 glad Exp $
– Procedure uses NTBACKUP for creation backup copy of the current database on a disk or a tape
– Using the NTBACKUP program for making BACKUP of a last the BACKUP users database file  
– Start in a copied base context and starting MSSQLSERVER account context

CREATE PROCEDURE task_NTBackup 
 @BKSdestination    VARCHAR(250) = 
 ‘C:\Documents and Settings\Default User\Local Settings\Application Data
\Microsoft\Windows NT\NTBackup\data’
– Path to BKS file ,@BKSname VARCHAR(250) = ‘filelist.bks’ – BKS file name ,@destination VARCHAR(250) = ‘E:\MSSQL’ – If it is specified and @TAPEname is NULL, it is the folder where copying will be executed ,@TAPEname VARCHAR(250) = NULL – Specified system tape name, example: 4mm DDS AS BEGIN DECLARE @DBID INT ,@ErrorID INT ,@file_exists INT ,@command VARCHAR (600) ,@fullfilename VARCHAR (250) ,@ErrorEXEC VARCHAR (256) ,@name VARCHAR (50) ,@resultmsg VARCHAR (500) ,@testname VARCHAR (50) ,@filename VARCHAR (256) – Variables initialization SET @name = DB_NAME() SET @DBID = DB_ID() SET @resultmsg = ‘NT Backup was executed also check is successful’ SET @ErrorEXEC = NULL – Definition of a last file name with a backup copy made by the mantenance plan SELECT TOP 1 @fullfilename = physical_device_name FROM msdb..backupset bs, msdb..backupmediafamily bmf, msdb..backupmediaset bms WHERE bs.media_set_id=bms.media_set_id AND bms.media_set_id=bmf.media_set_id AND bs.database_name = @name AND bs.type=‘D’ ORDER BY backup_start_date DESC – Recording of copied file name in BKS-file SELECT @command = ‘BCP ‘ + ‘”SELECT ‘ + + @fullfilename + + ‘”‘+ ‘ queryout “‘ + @BKSdestination + ‘\’ + @BKSname + ‘” -S’+ @@servername+ ‘ -T -CACP -w’ EXEC @ErrorID = master..xp_cmdshell @command – Choice BACKUP device IF @TAPEname IS NULL BEGIN – Check for removal same backup copy file SELECT @command = @destination + ‘\’ + @name + ‘_’ + CONVERT(VARCHAR,GETDATE(),112) + ‘.bkf’ EXECUTE @ErrorID = master..xp_fileexist @command, @file_exists OUTPUT IF @file_exists = 1 BEGIN SELECT @command = ‘DEL “‘ + @command + ‘” /F /Q’ EXEC @ErrorID = master..xp_cmdshell @command END – Start NTBACKUP for BACKUP on disk SELECT @command = ‘ntbackup backup ‘ + ‘”@’ + @BKSdestination + ‘\’ + @BKSname + ‘” /f ‘ + ‘”‘ + @destination + ‘\’ + @name + ‘_’ + CONVERT(VARCHAR,GETDATE(),112) + ‘.bkf’ + ‘”‘ + ‘ /V:yes /r:no /l:f /hc:on’ EXEC @ErrorID = master..xp_cmdshell @command END ELSE BEGIN – Start NTBACKUP for BACKUP on tape SELECT @command = ‘ntbackup backup ‘ + ‘”@’ + @BKSdestination + ‘\’ + @BKSname + ‘”‘ + ‘ /n ‘ + ‘”‘ + CONVERT(VARCHAR,GETDATE(),112) + ‘ ‘ + @name + ‘”‘ + ‘ /d “Set for: ‘ + @name + ‘ at ‘ + CONVERT(VARCHAR,GETDATE(),120) + ‘”‘ + ‘ /J “‘ + @name + CONVERT(VARCHAR,GETDATE(),120) + ‘”‘ + ‘ /V:yes /r:yes /rs:yes /hc:on /m normal /l:s ‘ + ‘ /P “‘ + @TAPEname + ‘”‘ + ‘ /um’ EXEC @ErrorID = master..xp_cmdshell @command END – Check return codes IF (@ErrorID <> 0) BEGIN IF @ErrorID = 1 SELECT @resultmsg = ‘NT Backup Error: ‘ + CONVERT(VARCHAR(2),@ErrorID) + ‘ NTBACKUP executions Error’ IF @ErrorID = 31 SELECT @resultmsg = ‘NT Backup Error: ‘ + CONVERT(VARCHAR(2),@ErrorID) + ‘ NTBACKUP does not find BACKUP device’ IF @ErrorID <> 31 AND @ErrorID <> 1 SELECT @resultmsg = ‘NT Backup Error: ‘ + CONVERT(VARCHAR(2),@ErrorID) – Check NTBACKUP Log on presence of error messages SELECT @command = ‘DIR “‘ + @BKSdestination + ‘”\*.LOG /B /O-D /X’ CREATE TABLE #temp (row_ident int IDENTITY (1, 1) NOT NULL, filesname varchar(256) NULL) INSERT INTO #temp EXEC master..xp_cmdshell @command SELECT TOP 1 @filename = filesname FROM #temp TRUNCATE TABLE #temp SET @command = ‘TYPE “‘ + @BKSdestination + ‘\’ + @filename + ‘”‘ INSERT INTO #temp EXEC master..xp_cmdshell @command SELECT TOP 1 @ErrorEXEC = filesname FROM #temp WHERE filesname LIKE ‘%Error%’ OR filesname LIKE ‘%error%’ OR filesname LIKE ‘%The operation did not successfully complete%’ OR filesname LIKE ‘%The operation was not performed%’ SET @resultmsg = @ErrorEXEC DROP TABLE #temp GOTO ERROR_EXIT END PRINT @resultmsg RETURN(0) ERROR_EXIT: PRINT @resultmsg RAISERROR (‘NT Backup Error for DB ID is:%d, and name: %s.’, 16, 1, @DBID, @name) RETURN (1) – ERROR OCCURED — END


It is desirable to use supervising JOB. This JOB should interrupt job NTBACKUP in case of lag. For this purpose it is possible to use the utility KILL.EXE
For this purpose you can use these scripts:


– $CARDS: mssql/tools/task_StopJob.sql,v 1.1 2004/08/06 18:05:56 glad Exp $
/*************************************************************************/
/*                                                                       */
/*  Procedure which is started by the separate task                      */
/*  for the control of duration of performance of other task             */
/*  which name is underlined in a variable @vJobName.                    */
/*                                                                       */
/*  December, 25 2002?. Moscow. The author of a script Gennady Kobzarev  */
/*************************************************************************/


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE task_StopJob @vJobName     SYSNAME,
                              @vMaxInterval INT
AS
BEGIN
 DECLARE @vJobId       uniqueidentifier
        ,@vRunDuration int
        ,@vValue       decimal(5,2)
        ,@vBeginTime   datetime
        ,@vCurrentTime datetime
        ,@vEndTime     datetime
        ,@vJobStatus   int
 
 SET    @vBeginTime   = GetDate() 
 SET    @vEndTime     = dateadd(ss, @vMaxInterval, GetDate())
 SELECT @vJobId       = job_id FROM msdb.dbo.sysjobs WHERE name = @vJobName
 SELECT @vRunDuration = msdb.dbo.Duration_In_Seconds(last_run_duration)
 FROM msdb.dbo.sysjobservers where job_id = @vJobId CREATE TABLE #tTmp ( job_id uniqueidentifier ,last_run_date int ,last_run_time int ,next_run_date int ,next_run_time int ,next_run_schedule_id int ,requested_to_run int ,request_source int ,request_source_id nvarchar(200) ,running int ,current_execution_step int ,current_retry_attempt int ,current_execution_status int ) SET @vCurrentTime = dateadd(ss, @vRunDuration, @vBeginTime) WHILE 1=1 BEGIN /*Parameters of the task in earlier created temporary table are read out*/ INSERT INTO #tTMP EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ‘sa’, @vJobId /*The status of performance of the task is determined*/ SELECT @vJobStatus = current_execution_status FROM #tTmp /*If the task is carried out (the status – 1) and has passed maximal time,*/ IF @vJobStatus in (0, 1, 2, 3, 7) and GetDate() >= @vEndTime BEGIN /* execution the task stops and error is generated.*/ exec msdb.dbo.sp_stop_job @job_id = @vJobId raisError(‘The maximal interval is exceeded!’, 16, 1) BREAK return END IF @vJobStatus in (4, 5) BREAK IF @vJobStatus in (6, 8) BEGIN exec msdb.dbo.sp_stop_job @job_id = @vJobId raisError(‘6, 8!’, 16, 1) BREAK return END DELETE FROM #tTmp /* Wait one minute */ WAITFOR DELAY ‘000:01:00′ END END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

 


– $CARDS: mssql/tools/UDF_Duration_In_Seconds.sql,v 1.1 2004/08/06 18:07:21 glad Exp $
/*************************************************************************/
/*                                                                       */
/* Function of recalculation of duration of the task execution in seconds*/
/*                                                                       */
/* December, 25 2002?. Moscow. The author of a script Gennady Kobzarev   */
/*************************************************************************/
USE msdb
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function dbo.Duration_In_Seconds (@vDuration int)
returns int
as
BEGIN
 declare @vDuration_In_Seconds int
 declare @vDuration_String varchar(6)

 set @vDuration_String = convert(varchar(6), @vDuration)

 if len(cast(@vDuration_String as varchar)) = 1 
  set @vDuration_In_Seconds = @vDuration

 if len(cast(@vDuration_String as varchar)) = 2 
  set @vDuration_In_Seconds = @vDuration

 if len(cast(@vDuration_String as varchar)) = 3 
  set @vDuration_In_Seconds = 
  convert(int, substring(@vDuration_String, 1, 1)) * 60 + 
  convert(int, substring(@vDuration_String, 2, 2))

 if len(cast(@vDuration_String as varchar)) = 4 
  set @vDuration_In_Seconds = 
  convert(int, substring(@vDuration_String, 1, 2)) * 60 + 
  convert(int, substring(@vDuration_String, 3, 2))

 if len(cast(@vDuration_String as varchar)) = 5 
  set @vDuration_In_Seconds = 
  convert(int, substring(@vDuration_String, 1, 1)) * 3600 + 
  convert(int, substring(@vDuration_String, 2, 2)) * 60 + 
  convert(int, substring(@vDuration_String, 4, 2))

 if len(cast(@vDuration_String as varchar)) = 6 
  set @vDuration_In_Seconds = 
  convert(int, substring(@vDuration_String, 1, 2)) * 3600 + 
  convert(int, substring(@vDuration_String, 3, 2)) * 60 + 
  convert(int, substring(@vDuration_String, 5, 2))
 RETURN(@vDuration_In_Seconds)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO 

In SQL Server 2000 is not documented but used at check of passwords function: pwdencrypt and pwdcompare.
I have decided to check up compatibility of these functions for SQL Server 2005.
The first, that is evident, it that hash became twice shorter, that most likely consequence of refusal of case-insensitive passwords.
Nevertheless, old hash quite well pass check by new functions.
One more appreciable difference, it that hash the same password now always visually different.
In an example, the third inserted row contains hash, taken of result of execution pwdencrypt on SQL Server 2000 SP3a 8.00.944.


USE TEMPDB
GO
declare @hash varbinary (255)
CREATE TABLE tempdb..h (id_num int, hash varbinary (255))
SET @hash = pwdencrypt('123') -- encryption
INSERT INTO tempdb..h (id_num,hash) VALUES (1,@hash)
SET @hash = pwdencrypt('123')
INSERT INTO tempdb..h (id_num,hash) VALUES (2,@hash)
SELECT TOP 1 @hash = hash FROM tempdb..h WHERE id_num = 2
SELECT pwdcompare ('123', @hash) AS [Success of check] -- Comparison
SELECT * FROM tempdb..h
INSERT INTO tempdb..h (id_num,hash) 
VALUES (3,CONVERT(varbinary (255),
0x01002D60BA07FE612C8DE537DF3BFCFA49CD9968324481C1A8A8FE612C8DE537DF3BFCFA49CD9968324481C1A8A8))
SELECT TOP 1 @hash = hash FROM tempdb..h WHERE id_num = 3
SELECT pwdcompare ('123', @hash) AS [Success of check] -- Comparison
SELECT * FROM tempdb..h
DROP TABLE tempdb..h
GO

Results


(1 row(s) affected)
(1 row(s) affected)
Success of check ------------------- 1 (1 row(s) affected)
id_num hash ----------- ------------------------------------------------------ 1 0x01004A335DCEDB366D99F564D460B1965B146D6184E4E1025195 2 0x0100E11D573F359629B344990DCD3D53DE82CF8AD6BBA7B638B6 (2 row(s) affected)
(1 row(s) affected)
Success of check
-------------------
1
(1 row(s) affected)
id_num hash ----------- ------------------------------------------------------ 1 0x01004A335DCEDB366D99F564D460B1965B146D6184E4E1025195 2 0x0100E11D573F359629B344990DCD3D53DE82CF8AD6BBA7B638B6 3 0x01002D60BA07FE612C8DE537DF3BFCFA49CD9968324481C1A8A8FE612C8DE537DF3BFCFA49CD9968324481C1A8A8 (3 row(s) affected)

Excuse for my English. I shall be glad to hear remarks.