quote:

---

Originally posted by crazyfisher

Hello ALL,
could anybody help me out?
after I have done a full backup of a database, I need to do a log backup and append it to the previous backup file, do i need to create another backup device for the log backup?
I backup the log to the same backup device and fail to restore the whole database through the log backup...
what stupid thing i have done?
could any one tell me the correct way to do the full+log backup ??
thanks very much !!

---

quote:

---

Originally posted by crazyfisher



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

---