Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Fundemantal Tran Log Questions

Author  Topic 

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-01-11 : 05:56:14
Greetings to all.

Please bear in mind that I am not a highly experienced DBA, but I am responsible for some governmental databases, hence my questions and concern. These questions have been festering in my mind for a while.

When I perform a search on a subject, I always stumble across DBAs mentioning how often they back up their transactional log. They talk of backing it up every couple of hours, or twice or three times a day etc.

Here's my first question.
Is there something wrong with backing up the tran log every 10 minutes? It seems common practise NOT to backup the tran log as frequently as this. But why not. If the database fails, the first thing one does (so I've read) is back up the existing tran log, then do your restores. This why you restore to the point in failure. However, if you're database has just failed, how do you know you'll be able to back up the existing tran log. I mean, at that stage it's an unknown whether you will be able to or not. That's why, if you were backing up the tran log every 10 minutes, you'll have only lost up to ten minutes data. But if most back it up once or twice a day, well tell me what company can afford to lose more than ten minutes data. I thought being able to restore as much of a companys mission critical data is one of the most important of DBA tasks. I mean we're talking losing our jobs. So why is it not common practise to back up the tran log as frequently as every 10 minutes.

Question 2
As a follow up to the above question, how often do major banks, such as Natwest Westminster, Lloyds, Barclays back up their transactional logs (I'm thinking cash withdrawals etc).

Question3
Another thing I keep stumbling across, is that it is bad practise to have no limit on transactional growth size. This, I don't really understand. If this were the case, why would Microsoft have implemented this feature into 2000, when it must have been a problem for DBAs in version 7. If the log is backed up every ten minutes as discussed above, then it's size would be kept at a minimum and the chances of it filling up disk space would be minimal itself.

Thanks. I eagerly await your responses!

Drew



spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-11 : 06:02:04
1. we back tran logs every 2 hours. people here back them up from once a day to every 10 minutes
2. don't know that one... but i don't think that trans logs are the only form of backup here in this case...
3. well that depends on how big your transactions are. if you do a huge amount of insert data in one transaction then your tran log will grow. so i wouldn't limit the growth size.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-01-11 : 06:13:36
quote:
Go with the flow & have fun! Else fight the flow


Not quite there yet.
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-01-11 : 06:32:30
re Q1....the need to backup every minute/hour/day is determined by the cost/effort of backups and the value/overhead the extra backups add to the restore process versus the cost of losing data changes between backups. Low volume/value DB activity => low volume backups.

re Q2....Banks use mirroring software to create duplicate images of transactions, and also use Tandem non-stop services to give 100% uptime. Information gleaned down through the years leans me to the view that that few would be using 'rollback enabled' databases...like SQl...most would use IMS.

re Q3....Microsoft does because Microsoft can....(just like governments I suppose)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-11 : 09:31:48

1. I set all transactional databases to backup at least every 15 minutes. I don't worry about the number of logs that I have to restore in case of a failure, because I wrote a script that will look at a directory, get a list of transaction log files, and generate log restores from that. That is something that it is good to have before you need it.

2. Don't know about banks.

3. I don't see anything wrong with unlimited growth. I would rather let it get large, than cause a processing failure because it could not grow. You do have to understand your application, and plan for what you need.



CODO ERGO SUM
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-01-11 : 09:48:03
Michael, that makes me feel a bit better, as that's what I'm doing. Though, I don't understand the need for the script in point 1, as Enterprise Manager lists all the transactional logs in order after the last complete backup (doesn't it?). What advantage does the scripts have?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-11 : 10:42:21
quote:
Originally posted by drewsalem

Michael, that makes me feel a bit better, as that's what I'm doing. Though, I don't understand the need for the script in point 1, as Enterprise Manager lists all the transactional logs in order after the last complete backup (doesn't it?). What advantage does the scripts have?



I never do restores via Enterprise Manager; I always use scripts in Query Analyzer. I won't go into all the reasons why this is a good idea, but just say that you should learn to do them that way. I think if you ask that other people who post here, you will find that they feel the same way.




CODO ERGO SUM
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-12 : 03:47:24
quote:
Originally posted by drewsalem

Michael, that makes me feel a bit better, as that's what I'm doing. Though, I don't understand the need for the script in point 1, as Enterprise Manager lists all the transactional logs in order after the last complete backup (doesn't it?). What advantage does the scripts have?



To rely on EM for the log list assumes that you will not lose your msdb database, amongst other things.

-------
Moo. :)
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-01-12 : 04:27:35
Ok, well you've all got me worried now. So what doe sthis script do? List all the Tran logs in order? and then restores them all in one execution?
Go to Top of Page

2lazydba
Yak Posting Veteran

62 Posts

Posted - 2006-01-12 : 13:16:22
i would also like 2 c the script which Michael Valentine Jones
is talking about...

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-01-12 : 13:58:40
"That is something that it is good to have before you need it"

Congratulations on receipt of "Kristen's Award for Understatement of the Month"

"Enterprise Manager lists all the transactional logs in order after the last complete backup"

So .. assumptions: you TLog backup every 10-15 minutes, and you have several databases on the server. You retain a "reasonable" history of backups.

Question: When you go into EM to make a restore of perhaps 20 transaction logs (4 hours-ish) how long does it take for everything to be displayed, you to select what you want, and EM to start restoring?

Here it takes a Month of Sundays, and we have some pretty fancy servers [in EM]. When I'm restoring TLogs on a Live server I know that time-is-of-the-essence ...

... and that I'll be under pressure, so I'll need clear data about when the backups where made, whether the ones I've got are contiguous (or whether some prat made a one-off backup and moved the file to their C: drive ) and so on.

The scripts we use are ones that I'm familiar with, give me information in a way I (and my team in my absence) have got comfortable with and have confidence in.

And they'll work on the brand-new-replacement server that we've just have to install - as mr_mist illuded to

Kristen
Go to Top of Page

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2006-01-12 : 15:58:33
Ok, well thanks for scaring the Bejeesus out of me. You're pretty good at doing that, I've noticed in this forum, Kristen. So is this the kind of script you're refering to?


Description:

Many DBA's have a Maintenance plan running that creates a backup every night and a transaction log backup every hour.

Restoring them to another server (for whatever reason) can be a tedious job. That is why I wrote this T-SQL script.

It will read the contents of a backup directory created by the maintenance plan and create a script needed to restore the entire database.


SET NOCOUNT ON
DECLARE @BACKUPDIR AS NVARCHAR(255) -- THE DIRECTORY WHERE THE ACKUPS ARE PLACED
DECLARE @RESTORESET AS INTEGER -- THE BACKUPSET YOU WANT TO RESTORE
DECLARE @NEWDBNAME AS VARCHAR(200) -- THE NEW DATABASE NAME
DECLARE @ORIGDBNAME AS VARCHAR(200) -- THE ORIGINAL DATABASE NAME
DECLARE @ORIGLGNAME AS VARCHAR(200) -- THE ORIGINAL LOG FILE NAME
DECLARE @NEWDEST AS VARCHAR(255) -- THE NEW DESTINATION DIRECTORY

SET @BACKUPDIR = 'F:\MSSQL\MSSQL\BACKUP\NORTHWIND\'
SET @RESTORESET = 2 -- RESTORE THE SECOND BACKUPSET FOUND IN THE DIRECTORY
SET @NEWDBNAME = 'NORTHWIND'
SET @ORIGDBNAME = 'NORTHWIND'
SET @ORIGLGNAME = 'NORTHWIND_LOG'
SET @NEWDEST = 'F:\MSSQL\MSSQL\DATA\'

DECLARE @DB_FILENAME_PRFX AS VARCHAR(200)
DECLARE @LG_FILENAME_PRFX AS VARCHAR(200)

CREATE TABLE #DIRECTORYTABLE
( ID INT IDENTITY,
SUBDIRECTORY SYSNAME,
DEPTH INTEGER,
[FILE] INTEGER,
SORTNAME VARCHAR(200),
SORTTIMESTR VARCHAR(30),
SORTTIME DATETIME)

-- GET THE DIRECTORY INFORMATION
INSERT #DIRECTORYTABLE (SUBDIRECTORY,DEPTH,[FILE])
EXECUTE MASTER.DBO.XP_DIRTREE @BACKUPDIR, 1, 1

-- GET THE FILE NAMES
SELECT TOP 1 @DB_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'')
FROM #DIRECTORYTABLE
WHERE SUBDIRECTORY LIKE '%.BAK'
SELECT TOP 1 @LG_FILENAME_PRFX = REPLACE(SUBDIRECTORY,RIGHT(SUBDIRECTORY,16),'')
FROM #DIRECTORYTABLE
WHERE SUBDIRECTORY LIKE '%.TRN'


-- GET THE TIMESTRING COINTAINED IN THE FILENAME AND UPDATE THE TABLE
UPDATE #DIRECTORYTABLE
SET SORTNAME = @DB_FILENAME_PRFX ,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@DB_FILENAME_PRFX,''),'.BAK','')
WHERE SUBDIRECTORY LIKE @DB_FILENAME_PRFX+'%'

UPDATE #DIRECTORYTABLE
SET SORTNAME = @LG_FILENAME_PRFX ,SORTTIMESTR = REPLACE(REPLACE(SUBDIRECTORY,@LG_FILENAME_PRFX,''),'.TRN','')
WHERE SUBDIRECTORY LIKE @LG_FILENAME_PRFX+'%'

-- NOW CREATE A MEMORYTABLE AND FORMAT THE DATA AS NEEDED
DECLARE @MEMDIR TABLE (ID INT IDENTITY,[FILENAME] VARCHAR(200),SORTNAME VARCHAR(200),SORTTIMESTR CHAR(12),BACKUPSET INTEGER)

-- INSERT THE NEEDED VALUES IN THE CORRECT ORDER
INSERT @MEMDIR ([FILENAME],SORTNAME,SORTTIMESTR) SELECT SUBDIRECTORY,SORTNAME,SORTTIMESTR FROM #DIRECTORYTABLE ORDER BY SORTTIMESTR

-- DROP THE TEMP TABLE
DROP TABLE #DIRECTORYTABLE

-- DECLARE SOME OTHER NEEDED VARIABLES
DECLARE @NAME VARCHAR(200)
DECLARE @CURRENT INTEGER
DECLARE @MAX INTEGER
DECLARE @BACKUPSET INTEGER
DECLARE @CURRENTSET INTEGER
DECLARE @BCKFNAME VARCHAR(200)
DECLARE @MAXID INTEGER


-- ENUMERATE THE BACKUP SET AND DETERMINE HOW MANY SETS THERE ARE AND NUMBER THEM
SET @CURRENT = 1
SET @BACKUPSET = 0
SELECT @MAX = MAX(ID) FROM @MEMDIR
WHILE @CURRENT <= @MAX
BEGIN
SELECT @NAME=SORTNAME FROM @MEMDIR WHERE ID = @CURRENT
IF @NAME = @DB_FILENAME_PRFX
BEGIN
SET @BACKUPSET = @BACKUPSET + 1
END
UPDATE @MEMDIR SET BACKUPSET = @BACKUPSET WHERE ID = @CURRENT
SET @CURRENT = @CURRENT + 1
END


-- OK WE KNOW WHICH BACKUP TO RESTORE ENUMERATE THE BACKUPS AGAIN AND OUTPUT THE RESTORE STATEMENTS
SELECT @MAXID = MAX(ID) FROM @MEMDIR WHERE BACKUPSET = @RESTORESET
SET @CURRENT = 1
WHILE @CURRENT <= @MAX
BEGIN
SELECT @NAME=SORTNAME,@CURRENTSET=BACKUPSET,@BCKFNAME= [FILENAME] FROM @MEMDIR WHERE ID = @CURRENT
IF @CURRENTSET = @RESTORESET
BEGIN
IF @NAME = @DB_FILENAME_PRFX
BEGIN
PRINT 'RESTORE DATABASE [' + @NEWDBNAME + ']'
PRINT 'FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
PRINT 'WITH MOVE ''' + @ORIGDBNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '.MDF'','
PRINT ' MOVE ''' + @ORIGLGNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '_LOG.LDF'','
IF @CURRENT < @MAXID PRINT ' NORECOVERY'
IF @CURRENT = @MAXID PRINT ' RECOVERY'
END
ELSE
BEGIN
PRINT 'RESTORE LOG [' + @NEWDBNAME + ']'
PRINT ' FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
IF @CURRENT < @MAXID PRINT ' WITH NORECOVERY'
IF @CURRENT = @MAXID PRINT ' WITH RECOVERY -- STOPAT = ''YYYY-MM-DD UU:MM:SS.000'''
END
END
SET @CURRENT = @CURRENT + 1
END



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 05:45:12
"So is this the kind of script you're refering to?"

Yeah.

The ALL CAPS is ghastly!

XP_DIRTREE is undocumented

I disagree with some of the formatting (INSERT statement all on one line, for example)

I'm not sure it will handle multiple target drives (MDF on one and LDF on another, for example, or Multiple Logical files)

Not sure it will handle Differential backups.

The MOVE statements make assumptions about the Old / New Logical and Physical names - it would be better to use RESTORE HEADERONLY to find that information.

For completeness the the original source is:
http://www.sqlservercentral.com/scripts/contributions/1194.asp

But other than that its pretty good!

For a full-ish example of the syntax for RESTORE see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE+syntax

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-07 : 04:20:41
Possibly also of interest: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67285

And http://www.nigelrivett.net/SQLAdmin/s_RestoreLatestBackup.html
Go to Top of Page

alexkreyn
Starting Member

9 Posts

Posted - 2007-07-19 : 14:58:07
CREATE PROCEDURE [dbo].[spTurnON_XP_Cmd_Shell]
AS
BEGIN
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
END

GO

CREATE PROCEDURE [dbo].[spTurnOFF_XP_Cmd_Shell]
AS
BEGIN
EXECUTE sp_configure 'xp_cmdshell', '0'
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
END

GO

CREATE PROCEDURE [dbo].[spTurnON_XP_Send_Mail]
AS
BEGIN
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'Database Mail XPs', '1'
RECONFIGURE WITH OVERRIDE
END

GO

CREATE PROCEDURE [dbo].[spTurnOFF_XP_Send_Mail]
AS
BEGIN
EXECUTE sp_configure 'Database Mail XPs', '0'
RECONFIGURE WITH OVERRIDE
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
END

GO

CREATE procedure [dbo].[spBackUpDatabase]
@Path varchar(1000) ,
@Type varchar(4), -- Full / Log / Diff
@RetentionPeriod tinyint, -- after this period the full .bak will be deleted
@DeleteNotMatchedDirectories bit = 0,--this deleted automatically all directories which do not match DBName(use carefully),
@EmailAddress varchar(1000)='',--use simicoma if more then one recepient: person1@domain.com;person2@domain.com
@SendEmailOnSuccess bit =0,
@SendEmailOnFail bit = 0
AS
/*
Current procedure is created to back up databases.
When new Full back up created procedure creates new directory under "Path/DBName".
All differential(Diff) and Log backups go to the same route(most recent) until new full back up file will be created.
The procedure also remove old backups directoris using RetentionPeriod, removes old database directories(@DeleteNotMatchedDirectories).
Created DirectoryName has Today Day Name, like "Path\DBName\yyyymmdd"
If more then one full backup created at the same day new directory will be created with
name "Path\DBName\yyyymmdd_1"
So each directory must include the only full backup file ,differential and log backup files.
The name of files is represented by day and time in military format.
Example:
C:\MyPath\DBName\20070718\DBName_Full_20070718_111904.bak
C:\MyPath\DBName\20070718\DBName_Diff_20070718_112004.bak
C:\MyPath\DBName\20070718\DBName_Log_20070718_112204.bak
C:\MyPath\DBName\20070718_2\DBName_Full_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Diff_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Diff_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak
C:\MyPath\DBName\20070718_2\DBName_Log_20070718_121904_2.bak

Backup process information located also in DatabaseBackups table which
must be created in master database. eith next structure:
drop table DatabaseBackups
Create table DatabaseBackups
(
ID int NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1, 1),
DBName varchar(100),
BackUpFlag varchar(4) not null check (BackUpFlag in ('Full','Log','Diff')) ,
RetentionPeriod int null,
FullFileName varchar(1000) not null,
AuditDate datetime default GetDate()
)

procedure also sends emails to track process to specific emails.

*/
declare @sql varchar(1000),@sMsg varchar(8000)
declare @DatabaseBackup table(DBName varchar(128))
declare @TempTbl table(FieldName varchar(128))
declare @Count_OnSuccess tinyint, @Count_OnFail tinyint
If NOT @Type ='Full' AND Not @Type = 'Log' and Not @Type='Diff' GOTO Problems
set @Count_OnSuccess=0
set @Count_OnFail=0
set @Path=replace(replace(ltrim(rtrim(@Path)),'/','\'),'\\','\')
if Len(@Path)>0
Begin
if not right(@Path,1)='\' set @Path=@Path +'\'
end

insert @DatabaseBackup(DBName)
select [Name]
from sys.databases
where database_id in(7)
order by name
set @sql = 'dir /B /ad "' + @Path +'"'
exec spTurnON_XP_Cmd_Shell
insert @TempTbl exec master..xp_cmdshell @sql
exec spTurnOFF_XP_Cmd_Shell
DECLARE @SubDirName varchar(100)
DELETE FROM @TempTbl where FieldName IS NULL

--Remove old Directories
if @Type='Full' and @DeleteNotMatchedDirectories=1
BEGIN
IF (SELECT Count(FieldName)from @TempTbl where FieldName not IN(SELECT DBName from @DatabaseBackup))>0
BEGIN
declare ListOldDir cursor FAST_FORWARD FOR
select FieldName
from @TempTbl
where FieldName not IN(
SELECT DBName
from @DatabaseBackup)
OPEN ListOldDir
FETCH NEXT FROM ListOldDir
INTO @SubDirName
exec spTurnON_XP_Cmd_Shell
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'rmdir "' + @Path + @SubDirName +'" /s /q'
exec master..xp_cmdshell @sql
DELETE FROM DatabaseBackups where DBName = @SubDirName
FETCH NEXT FROM ListOldDir
INTO @SubDirName
END
exec spTurnOFF_XP_Cmd_Shell
CLOSE ListOldDir
DEALLOCATE ListOldDir
END
END

--Create New Directories
if(SELECT COUNT(DBName) from @DatabaseBackup where DBName not IN(SELECT FieldName from @TempTbl))>0
BEGIN
if Not @Type='Full'
begin
RAISERROR(14524, -1, -1, 'You must create full back up first')
return
end
declare ListNewDir cursor FAST_FORWARD FOR
select DBName
from @DatabaseBackup
where DBName not IN(
SELECT FieldName
from @TempTbl
)

OPEN ListNewDir
FETCH NEXT FROM ListNewDir
INTO @SubDirName
exec spTurnON_XP_Cmd_Shell
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'md "' + @Path + @SubDirName +'"'
exec master..xp_cmdshell @sql
FETCH NEXT FROM ListNewDir
INTO @SubDirName
END
exec spTurnOFF_XP_Cmd_Shell
CLOSE ListNewDir
DEALLOCATE ListNewDir

END
DELETE FROM @TempTbl

--Loop through Each DB
declare @DBName varchar(100)
declare @DBDirectory varchar(1200)
declare @LastBackupToKeep varchar(50)
DECLARE @TempDirName table(Dir varchar(50))
DECLARE @sTempDate varchar(50),@sTemp varchar(20),@sTempShort varchar(20)
declare ExistingDB cursor FAST_FORWARD FOR
select DBName
from @DatabaseBackup
order By DBName
OPEN ExistingDB
FETCH NEXT FROM ExistingDB
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DBDirectory = @Path + @DBName +'\'
IF @Type='Full'
BEGIN
--OLD Directories must be deleted
select @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)
set @sql = 'dir /B /ad "' + @DBDirectory +'"'
exec spTurnON_XP_Cmd_Shell
insert @TempTbl exec master..xp_cmdshell @sql
delete from @TempTbl where (FieldName is NULL OR FieldName>=convert(varchar(20),@LastBackupToKeep))
exec spTurnOFF_XP_Cmd_Shell
if(select Count(FieldName) from @TempTbl)>0
BEGIN
declare MoveOldDirectory cursor FAST_FORWARD FOR
select FieldName
from @TempTbl
OPEN MoveOldDirectory
FETCH NEXT FROM MoveOldDirectory
INTO @SubDirName
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = 'rmdir "' + @DBDirectory + @SubDirName +'" /s /q'
exec spTurnON_XP_Cmd_Shell
exec master..xp_cmdshell @sql
exec spTurnOFF_XP_Cmd_Shell
IF @@Error=0
BEGIN
delete from DatabaseBackups where FullFileName like @DBDirectory + @SubDirName +'%'
END
FETCH NEXT FROM MoveOldDirectory
INTO @SubDirName
END
CLOSE MoveOldDirectory
DEALLOCATE MoveOldDirectory
END
--NEW Directory must be created
set @sTempDate=LTRIM(RTRIM(convert(char(10),getdate() ,112)))
set @sTemp=''

--Check I the directory exists already...If yes create new directory

set @sql = 'dir /B /ad "' + @DBDirectory + @sTempDate +'*.*"'
exec spTurnON_XP_Cmd_Shell
insert @TempTbl exec master..xp_cmdshell @sql
exec spTurnOFF_XP_Cmd_Shell
DELETE FROM @TempTbl WHERE FieldName IS null
select @sTemp=Max(FieldName)
from @TempTbl
IF LEN(@sTemp)>0 AND NOT @sTemp='File Not Found'
BEGIN
IF (select CHARINDEX ( '_',@sTemp))=0
BEGIN
set @sTempDate = @sTempDate +'_1'
END
ELSE
BEGIN
delete from @TempTbl where LEN(FieldName)<=8 OR FieldName IS NULL
update @TempTbl set FieldName = Right(FieldName,len(FieldName)-9)
select @sTempDate=@sTempDate+'_' + Convert(varchar(50),Max(Convert(tinyint,FieldName))+1) from @TempTbl
END
END
select @sql = 'md "' + @DBDirectory + @sTempDate +'"'
exec spTurnON_XP_Cmd_Shell
exec master..xp_cmdshell @sql
exec spTurnOFF_XP_Cmd_Shell
set @sql = @DBDirectory + @sTempDate +'\' + @DBName + '_'
+ @Type + '_'+LEFT(@sTempDate,8) + '_'
+ replace(convert(varchar(8),getdate(),108),':','')
if len(@sTempDate)>8 set @sql=@sql+Right(@sTempDate,len(@sTempDate)-8)
set @sql =@sql+'.bak'
backup database @DBName to disk = @sql
if @@Error=0
BEGIN
INSERT DatabaseBackups(DBName,BackUpFlag,RetentionPeriod,FullFileName)
VALUES(@DBName,@Type,@RetentionPeriod,@sql)
set @Count_OnSuccess =@Count_OnSuccess+1
END
else
BEGIN
set @Count_OnFail =@Count_OnFail+1
set @sMsg = @sMsg +'Fail to create backup ' + @sql + Char(10)+Char(13)
END


END
ELSE
BEGIN
set @sql = 'dir /B /ad "' + @DBDirectory +'"'
exec spTurnON_XP_Cmd_Shell
insert @TempTbl exec master..xp_cmdshell @sql
exec spTurnOFF_XP_Cmd_Shell
DELETE FROM @TempTbl WHERE FieldName IS null
If (Select Count(FieldName) from @TempTbl)>0
BEGIN
set @sTemp=''
SELECT @sTemp=MAX(FieldName) FROM @TempTbl
IF LEN(@sTemp)=8
BEGIN
SET @sTempDate= @sTemp
set @sql = @DBDirectory + @sTempDate +'\'+ @DBName + '_'
+ @Type + '_'+ @sTempDate +'_'+ replace(convert(varchar(8),getdate(),108),':','')+'.bak'
END
ELSE
BEGIN
set @sTempShort=Left(@sTemp,8)
DELETE FROM @TempTbl
WHERE FieldName<@sTempShort
or Len(FieldName)<9

UPDATE @TempTbl
SET FieldName=Right(FieldName,len(FieldName)-9)
select @sTempDate = @sTempShort+'_'+Convert(varchar(20),Max(convert(tinyint,FieldName))) from @TempTbl
set @sql = @DBDirectory + @sTempDate +'\'+ @DBName + '_'
+ @Type + '_'+ @sTempShort +'_'+ replace(convert(varchar(8),getdate(),108),':','')+
Right(@sTempDate,len(@sTempDate)-8)+ '.bak'
END
if @Type='Diff'
BEGIN
backup database @DBName to disk = @sql WITH DIFFERENTIAL
if @@Error=0
BEGIN
INSERT DatabaseBackups(DBName,BackUpFlag,FullFileName)
VALUES(@DBName,@Type,@sql)
set @Count_OnSuccess =@Count_OnSuccess+1
END
else
BEGIN
set @Count_OnFail =@Count_OnFail+1
set @sMsg = @sMsg +'Fail to create backup ' + @sql + Char(10)+Char(13)
END
END
else if @Type = 'Log'
BEGIN
backup log @DBName to disk = @sql
if @@Error=0
BEGIN
INSERT DatabaseBackups(DBName,BackUpFlag,FullFileName)
VALUES(@DBName,@Type,@sql)
set @Count_OnSuccess =@Count_OnSuccess+1
END
else
BEGIN
set @Count_OnFail =@Count_OnFail+1
set @sMsg = @sMsg +'Fail to create backup ' + @sql + Char(10)+Char(13)
END
END
END
END
DELETE FROM @TempTbl
FETCH NEXT FROM ExistingDB
INTO @DBName
END
CLOSE ExistingDB
DEALLOCATE ExistingDB
if @EmailAddress<>''
BEGIN
Declare @Subject varchar(100)
if @SendEmailOnSuccess =1 and @Count_onFail=0
BEGIN
set @subject = 'SQL Server Backup Success Notification from server:'+@@ServerName
exec spTurnON_XP_Send_Mail
exec msdb.dbo.sp_send_dbmail @recipients=@EmailAddress,
@subject = @subject,
@body = @sMsg,
@query = 'SELECT * FROM DatabaseBackups',
@query_attachment_filename='BackupResult.txt',
@body_format = 'HTML',
@attach_query_result_as_file=1,
@query_result_separator='|';
exec spTurnOFF_XP_Send_Mail
END
if @SendEmailOnFail =1 and @Count_onFail>0
BEGIN
set @subject = 'SQL Server Backup Fail Notification from server:'+@@ServerName
exec spTurnON_XP_Send_Mail
exec msdb.dbo.sp_send_dbmail @recipients=@EmailAddress,
@subject = @subject,
@body = @sMsg,
@query = 'SELECT * FROM DatabaseBackups',
@query_attachment_filename='BackupResult.txt',
@body_format = 'HTML',
@attach_query_result_as_file=1,
@query_result_separator='|';
exec spTurnOFF_XP_Send_Mail
END
END

return
Problems:

GO
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-19 : 15:39:08
quote:
Originally posted by alexkreyn
... code omitted. ..


Do you have a question?

If so, please post it on a new topic.



CODO ERGO SUM
Go to Top of Page
   

- Advertisement -