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.