comment restaurer les differents backup des log

Bonjour,

Quelque chose comme ça ???


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
DROP PROCEDURE EXPSaveLog
go
CREATE PROCEDURE EXPSaveLog
@dbname varchar(30) = 'Saretec' , @BackupDir varchar(255) 'S:MSSQLBACKUP' ,
@TransactionFile varchar(255) OUTPUT AS
SET NOCOUNT ON
DECLARE @FullTransactionFile varchar(255)
DECLARE @hour varchar(2)
DECLARE @minute varchar(2)
SELECT @hour = DATENAME(hour,getdate())
IF DATALENGTH(@hour) < 2 SELECT @hour = '0' + @hour
SELECT @minute = DATENAME(minute,getdate())
IF DATALENGTH(@minute) < 2 SELECT @minute = '0' + @minute
SELECT @TransactionFile = @dbname + 'Log'
+ LTRIM(convert(varchar(30),getdate(),112)) -- Format ISO YYYYMMDD
+ '_' + @hour + @minute
+ '.DMP'
SELECT @FullTransactionFile = @BackupDir + '' + @TransactionFile
DUMP TRANSACTION @dbname TO DISK = @FullTransactionFile WITH STATS = 100,
INIT
go
DROP PROCEDURE EXPCopyLog
go

CREATE PROCEDURE EXPCopyLog
@dbname varchar(30)='Saretec' , @TransactionFile varchar(255) ,
@PrimaryServer varchar(30) ='C900SE15' , @PrimShareName varchar(255) = 'S$'
, @RemBackupDir varchar(255) = 'MSSQLBACKUP' ,
@BackupServer varchar(30) ='X040SS03' , @BackShareName varchar(255) = 'D$'
, @LocalBackupDir varchar(255) = 'MSSQLBACKUP' ,
@compress int = 0 AS
DECLARE @retcode int ,
@Command varchar(255) ,
@PrimaryTransactionFile varchar(255) ,
@FileExtension varchar(3) ,
@FileWithoutExtension varchar(255) ,
@CompTransactionFile varchar(255) ,
@ExePath varchar(255) ,
@SourcePath varchar(255)

select @FileExtension =RIGHT(@TransactionFile ,
datalength(@TransactionFile) - CHARINDEX('.', @TransactionFile ))
select @FileWithoutExtension =substring(@TransactionFile , 1, CHARINDEX('.',
@TransactionFile )-1)
if @compress = 1
begin
select @CompTransactionFile = @FileWithoutExtension + '.CAB'
select @ExePath ='C:Tempmcs' -- change your path Here !!!
select @SourcePath = '\' + @PrimaryServer + '' + @PrimShareName + '' +
@RemBackupDir
SELECT @Command = @ExePath + 'MAKECAB.EXE /L ' + @SourcePath + ' ' +
@SourcePath + '' + @TransactionFile + ' ' + @CompTransactionFile
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error compressing Transaction Log Backup File',16,-1)
return (1)
end
select @TransactionFile = @CompTransactionFile
end
SELECT @PrimaryTransactionFile = '\' + @PrimaryServer + '' +
@PrimShareName
+ '' + @RemBackupDir + '' + @TransactionFile
SELECT @TransactionFile = '\' + @BackupServer + '' +
@BackShareName
+ '' + @LocalBackupDir + '' + @TransactionFile
--SELECT @Command = 'del ' + @TransactionFile
--EXEC @retcode = master..xp_cmdshell @Command
--IF @retcode <> 0
--begin
-- raiserror('Error deleting Transaction Log Backup File',16,-1)
-- return (1)
--end
SELECT @Command = 'copy ' + @PrimaryTransactionFile + ' ' + @TransactionFile
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error copying Transaction Log Backup File',16,-1)
return (1)
end

go

DROP PROCEDURE EXPExtractLog
go

CREATE PROCEDURE EXPExtractLog
@CABBackupDir varchar(255) = 'D:MSSQLBACKUP' ,
@LogBackupDir varchar(255) = 'D:MSSQLBACKUP' ,
@CABCopyDir varchar(255) = 'D:MSSQLBACKUPCAB'
AS
DECLARE @retcode int ,
@Command varchar(255) ,
@CurrentCABFileName varchar(255),
@FileExtension varchar(3) ,
@ExePath varchar(255)

select @FileExtension = 'CAB'
select @ExePath ='C:WINNT' -- change your path Here !!!
-- Find all CAB file in the destination CAB directory
create table #CAB(CABFileName varchar(255))
select @Command = 'DIR ' + @CABBackupDir + '*.' + @FileExtension + ' /ON
/B'
insert #CAB exec master..xp_cmdshell @Command
declare c cursor for
SELECT * from #CAB
open c
fetch c into @CurrentCABFileName
while @@fetch_status =0
begin
-- Extract CAB file
if @CurrentCABFileName like '%Fichier introuvable%' break -- Output de
DIR si no match
SELECT @Command = @ExePath + 'extract.EXE /L ' + @LogBackupDir + ' /E /Y '
+ @CABBackupDir + '' + @CurrentCABFileName
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error Uncompressing file',16,-1)
break -- process next CAB file
end
-- Move CAB file to a copy directory waiting for purge
SELECT @Command = 'MOVE ' + @CABBackupDir + '' + @CurrentCABFileName + '
' + @CABCopyDir
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error Moving CAB file',16,-1)
break -- process next CAB file
end
fetch c into @CurrentCABFileName
end
close c
deallocate c
go


DROP PROCEDURE EXPRestoreLog
go
CREATE PROCEDURE EXPRestoreLog
@dbname varchar(30)='Saretec' , @LocalBackupDir varchar(255) 'D:MSSQLBACKUP' ,
@Localdbname varchar(30)='Saretec' , @compress int = 0 AS
SET NOCOUNT ON
DECLARE @hour varchar(2) ,
@minute varchar(2) ,
@TransactionFile varchar(255) ,
@FullTranFileName varchar(255) ,
@PrimaryTransactionFile varchar(255) ,
@LastRestoredTranFile varchar(255) ,
@LastRestoredDBFile varchar(255) ,
@retcode int ,
@Command varchar(255) ,
@LastRestoredDate datetime ,
@LastSaveDate datetime

-- Get the last Restored Log file from msdb history restore tables (6.5
only)
-- Take last TranFile restored after last Full DB restore
SELECT @LastRestoredTranFile = max(device_name) from msdb..sysrestorehistory
h , msdb..sysrestoredetail d
where h.restore_id = d.restore_id and source_database_name = @Localdbname
and backup_type = 2
and restore_date >
(select max(restore_date) from msdb..sysrestorehistory
where source_database_name = @Localdbname and backup_type = 1)
-- Disk volume has full pathname , get relative pathname from
@LocalBackupDir
declare @i int
select @i = datalength(@LastRestoredTranFile )
select @i = @i - datalength(@LocalBackupDir) -1
select @LastRestoredTranFile = right(@LastRestoredTranFile , @i)
-- Si Null : soit initialisation , soit d,marrer au premier LogBackup
suivant le dernier DBBackup
if @LastRestoredTranFile is null
begin
SELECT @LastRestoredDBFile = device_name from msdb..sysrestorehistory h ,
msdb..sysrestoredetail d
where h.restore_id = d.restore_id and source_database_name @Localdbname and backup_type = 1
and restore_date = (select max(restore_date) from
msdb..sysrestorehistory
where source_database_name = @Localdbname and backup_type = 1 )
select 'Last Full DB Restore : ' + @LastRestoredDBFile
if @LastRestoredDBFile is null
begin
raiserror('Il n''y a pas eu de restauration complSte de la base avant
restauration des Log',16,-1)
return 1
end
else
begin
SELECT @LastSaveDate = max(backup_start) from msdb..sysrestorehistory h
where source_database_name = @Localdbname and backup_type = 1
SELECT @hour = DATENAME(hour, @LastSaveDate )
IF DATALENGTH(@hour) < 2 SELECT @hour = '0' + @hour
SELECT @minute = DATENAME(minute, @LastSaveDate )
IF DATALENGTH(@minute) < 2 SELECT @minute = '0' + @minute
select @LastRestoredTranFile = @dbname + 'Log' +
convert(varchar(20),@LastSaveDate ,112) + '_' + @hour + @minute
end
end
-- Find all Log Backup newer than the one restored to be applied
select 'Last Log Restore : ' + @LastRestoredTranFile
declare @CurrentTranFileName varchar(255)
create table #Log(TranFileName varchar(255))
select @Command = 'DIR ' + @LocalBackupDir + '' + @dbname + 'Log' + '*.DMP
/ON /B'
insert #Log exec master..xp_cmdshell @Command
declare c cursor for
SELECT * from #Log where TranFileName > @LastRestoredTranFile
open c
fetch c into @CurrentTranFileName
while @@fetch_status =0
begin
-- Restore Tran in order
select 'Current Log Restore : ' + @CurrentTranFileName
select @FullTranFileName = @LocalBackupDir + '' + @CurrentTranFileName
LOAD TRANSACTION @Localdbname FROM DISK = @FullTranFileName WITH STATS 100
-- Rename file to avoid further duplicate restores
SELECT @Command = 'RENAME ' + @LocalBackupDir + '' + @CurrentTranFileName
+ ' ' +
+ '_' + @CurrentTranFileName
--print @command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Erreur au renommage du fichier de sauvegarde du Log',16,-1)
end
fetch c into @CurrentTranFileName
end
close c
deallocate c
drop table #Log
go


--dump database pubs to pubs with init
--insert t1 select * from t1
--ExpSaveLog 'Pubs' , 'C:MSSQL7BACKUP'

--LOAD database pubs2 from pubs
--insert t1 select * from t1
--ExpSaveLog 'Pubs2' , 'C:MSSQL7BACKUP'


DROP PROCEDURE EXPKillProcessOnBackup
go
CREATE PROCEDURE EXPKillProcessOnBackup @dbname varchar(30)='Saretec' AS
SET NOCOUNT ON
DECLARE @vc_spid varchar(20)
DECLARE Cursor_ProcessOnCandidate
INSENSITIVE
CURSOR FOR
SELECT CONVERT(varchar(20),spid)
FROM master..sysprocesses
WHERE dbid= DB_ID(@dbname)
OPEN Cursor_ProcessOnCandidate
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM Cursor_ProcessOnCandidate
INTO @vc_spid
IF (@@fetch_status <> 0)
BEGIN
DEALLOCATE Cursor_ProcessOnCandidate
BREAK
END
EXEC ('KILL ' + @vc_spid)
END
go

DROP PROCEDURE EXPPurgeLogPrim
go
CREATE PROCEDURE EXPPurgeLogPrim
@dbname varchar(30) = 'Saretec' ,
@BackupDir varchar(255) = 'D:MSSQLBACKUP'
AS
DECLARE @retcode int ,
@Command varchar(255)
-- Purge des fichiers de sauvegarde (<Base>LogYYYYMMDD_HHMM.DMP &
<Base>LogYYYYMMDD_HHMM.CAB)
SELECT @Command = 'DEL ' + @BackupDir + '' + @dbname +'Log' + '*.CAB'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting CAB Compressed Log Backup File',16,-1)
return (1)
end
SELECT @Command = 'DEL ' + @BackupDir + '' + @dbname +'Log' + '*.DMP'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting Transaction Log Backup File',16,-1)
return (1)
end
GO

DROP PROCEDURE EXPPurgeLogBack
go
CREATE PROCEDURE EXPPurgeLogBack
@dbname varchar(30) = 'Saretec' ,
@BackupDir varchar(255) = 'D:MSSQLBACKUP' ,
@CABBackupDir varchar(255) = 'D:MSSQLBACKUPCAB'
AS
DECLARE @retcode int ,
@Command varchar(255)
-- Purge des fichiers compress, historis,s (<Base>LogYYYYMMDD_HHMM.CAB)
SELECT @Command = 'DEL ' + @CABBackupDir + '' + @dbname +'Log' + '*.CAB'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting CAB Compressed Log Backup File',16,-1)
return (1)
end
-- Purge des fichiers Log restaur,s historis,s (_<Base>LogYYYYMMDD_HHMM.DMP)
SELECT @Command = 'DEL ' + @BackupDir + '_' + @dbname +'Log' + '*.DMP'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting Transaction Log Backup File',16,-1)
return (1)
end
GO

Phil.


"M'bark BOULOUIRD" <bark.news@DELETEspam.logaviv.com> wrote in message
news:#xRbagv0EHA.1188@tk2msftngp13.phx.gbl...

bonjour,

sous SQL server 2000 standard sp3

j'effectue automatiquement des sauvegardes du Journal des transacation

avec

truncate, j'ajoute les backup au fur et a mesure sur le meme device

BACKUP LOG [logship] TO [logship_log_backup_device] WITH NOINIT ,

NOUNLOAD

, NAME = N'logship backup', NOSKIP , STATS = 10, NOFORMAT

mais voila je souhaiterais creer un script qui me restaure tous mes backup
de JT au moment voulu. ( j'ai trois backup dans mon device , je voudrais
qu'il me joue les troi backup dans l'ordre).


comment faire pour automatiser la tache( sans entreprise manager)?

merci d'avance.

M'bark

ahhh oui merci, j'ai bien du boulot

mais merci beaucoup c tres gentil,

je vais me plonger dans la lecture de votre code SQL

merci

M'bark


"Philippe [MS]" <ptrotin@online.microsoft.com> a écrit dans le message de
news: OWmXejv0EHA.1860@TK2MSFTNGP15.phx.gbl...

Bonjour,

Quelque chose comme ça ???


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
DROP PROCEDURE EXPSaveLog
go
CREATE PROCEDURE EXPSaveLog
@dbname varchar(30) = 'Saretec' , @BackupDir varchar(255) > 'S:MSSQLBACKUP' ,
@TransactionFile varchar(255) OUTPUT AS
SET NOCOUNT ON
DECLARE @FullTransactionFile varchar(255)
DECLARE @hour varchar(2)
DECLARE @minute varchar(2)
SELECT @hour = DATENAME(hour,getdate())
IF DATALENGTH(@hour) < 2 SELECT @hour = '0' + @hour
SELECT @minute = DATENAME(minute,getdate())
IF DATALENGTH(@minute) < 2 SELECT @minute = '0' + @minute
SELECT @TransactionFile = @dbname + 'Log'
+ LTRIM(convert(varchar(30),getdate(),112)) -- Format ISO YYYYMMDD
+ '_' + @hour + @minute
+ '.DMP'
SELECT @FullTransactionFile = @BackupDir + '' + @TransactionFile
DUMP TRANSACTION @dbname TO DISK = @FullTransactionFile WITH STATS = 100,
INIT
go
DROP PROCEDURE EXPCopyLog
go

CREATE PROCEDURE EXPCopyLog
@dbname varchar(30)='Saretec' , @TransactionFile varchar(255) ,
@PrimaryServer varchar(30) ='C900SE15' , @PrimShareName varchar(255) =
'S$'
, @RemBackupDir varchar(255) = 'MSSQLBACKUP' ,
@BackupServer varchar(30) ='X040SS03' , @BackShareName varchar(255) = 'D$'
, @LocalBackupDir varchar(255) = 'MSSQLBACKUP' ,
@compress int = 0 AS
DECLARE @retcode int ,
@Command varchar(255) ,
@PrimaryTransactionFile varchar(255) ,
@FileExtension varchar(3) ,
@FileWithoutExtension varchar(255) ,
@CompTransactionFile varchar(255) ,
@ExePath varchar(255) ,
@SourcePath varchar(255)

select @FileExtension =RIGHT(@TransactionFile ,
datalength(@TransactionFile) - CHARINDEX('.', @TransactionFile ))
select @FileWithoutExtension =substring(@TransactionFile , 1,
CHARINDEX('.',
@TransactionFile )-1)
if @compress = 1
begin
select @CompTransactionFile = @FileWithoutExtension + '.CAB'
select @ExePath ='C:Tempmcs' -- change your path Here !!!
select @SourcePath = '\' + @PrimaryServer + '' + @PrimShareName + '' +
@RemBackupDir
SELECT @Command = @ExePath + 'MAKECAB.EXE /L ' + @SourcePath + ' ' +
@SourcePath + '' + @TransactionFile + ' ' + @CompTransactionFile
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error compressing Transaction Log Backup File',16,-1)
return (1)
end
select @TransactionFile = @CompTransactionFile
end
SELECT @PrimaryTransactionFile = '\' + @PrimaryServer + '' +
@PrimShareName
+ '' + @RemBackupDir + '' + @TransactionFile
SELECT @TransactionFile = '\' + @BackupServer + '' +
@BackShareName
+ '' + @LocalBackupDir + '' + @TransactionFile
--SELECT @Command = 'del ' + @TransactionFile
--EXEC @retcode = master..xp_cmdshell @Command
--IF @retcode <> 0
--begin
-- raiserror('Error deleting Transaction Log Backup File',16,-1)
-- return (1)
--end
SELECT @Command = 'copy ' + @PrimaryTransactionFile + ' ' +
@TransactionFile
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error copying Transaction Log Backup File',16,-1)
return (1)
end

go

DROP PROCEDURE EXPExtractLog
go

CREATE PROCEDURE EXPExtractLog
@CABBackupDir varchar(255) = 'D:MSSQLBACKUP' ,
@LogBackupDir varchar(255) = 'D:MSSQLBACKUP' ,
@CABCopyDir varchar(255) = 'D:MSSQLBACKUPCAB'
AS
DECLARE @retcode int ,
@Command varchar(255) ,
@CurrentCABFileName varchar(255),
@FileExtension varchar(3) ,
@ExePath varchar(255)

select @FileExtension = 'CAB'
select @ExePath ='C:WINNT' -- change your path Here !!!
-- Find all CAB file in the destination CAB directory
create table #CAB(CABFileName varchar(255))
select @Command = 'DIR ' + @CABBackupDir + '*.' + @FileExtension + ' /ON
/B'
insert #CAB exec master..xp_cmdshell @Command
declare c cursor for
SELECT * from #CAB
open c
fetch c into @CurrentCABFileName
while @@fetch_status =0
begin
-- Extract CAB file
if @CurrentCABFileName like '%Fichier introuvable%' break -- Output de
DIR si no match
SELECT @Command = @ExePath + 'extract.EXE /L ' + @LogBackupDir + ' /E /Y
'
+ @CABBackupDir + '' + @CurrentCABFileName
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error Uncompressing file',16,-1)
break -- process next CAB file
end
-- Move CAB file to a copy directory waiting for purge
SELECT @Command = 'MOVE ' + @CABBackupDir + '' + @CurrentCABFileName + '
' + @CABCopyDir
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error Moving CAB file',16,-1)
break -- process next CAB file
end
fetch c into @CurrentCABFileName
end
close c
deallocate c
go


DROP PROCEDURE EXPRestoreLog
go
CREATE PROCEDURE EXPRestoreLog
@dbname varchar(30)='Saretec' , @LocalBackupDir varchar(255) > 'D:MSSQLBACKUP' ,
@Localdbname varchar(30)='Saretec' , @compress int = 0 AS
SET NOCOUNT ON
DECLARE @hour varchar(2) ,
@minute varchar(2) ,
@TransactionFile varchar(255) ,
@FullTranFileName varchar(255) ,
@PrimaryTransactionFile varchar(255) ,
@LastRestoredTranFile varchar(255) ,
@LastRestoredDBFile varchar(255) ,
@retcode int ,
@Command varchar(255) ,
@LastRestoredDate datetime ,
@LastSaveDate datetime

-- Get the last Restored Log file from msdb history restore tables (6.5
only)
-- Take last TranFile restored after last Full DB restore
SELECT @LastRestoredTranFile = max(device_name) from
msdb..sysrestorehistory
h , msdb..sysrestoredetail d
where h.restore_id = d.restore_id and source_database_name = @Localdbname
and backup_type = 2
and restore_date >
(select max(restore_date) from msdb..sysrestorehistory
where source_database_name = @Localdbname and backup_type = 1)
-- Disk volume has full pathname , get relative pathname from
@LocalBackupDir
declare @i int
select @i = datalength(@LastRestoredTranFile )
select @i = @i - datalength(@LocalBackupDir) -1
select @LastRestoredTranFile = right(@LastRestoredTranFile , @i)
-- Si Null : soit initialisation , soit d,marrer au premier LogBackup
suivant le dernier DBBackup
if @LastRestoredTranFile is null
begin
SELECT @LastRestoredDBFile = device_name from msdb..sysrestorehistory h ,
msdb..sysrestoredetail d
where h.restore_id = d.restore_id and source_database_name > @Localdbname and backup_type = 1
and restore_date = (select max(restore_date) from
msdb..sysrestorehistory
where source_database_name = @Localdbname and backup_type = 1 )
select 'Last Full DB Restore : ' + @LastRestoredDBFile
if @LastRestoredDBFile is null
begin
raiserror('Il n''y a pas eu de restauration complSte de la base avant
restauration des Log',16,-1)
return 1
end
else
begin
SELECT @LastSaveDate = max(backup_start) from msdb..sysrestorehistory h
where source_database_name = @Localdbname and backup_type = 1
SELECT @hour = DATENAME(hour, @LastSaveDate )
IF DATALENGTH(@hour) < 2 SELECT @hour = '0' + @hour
SELECT @minute = DATENAME(minute, @LastSaveDate )
IF DATALENGTH(@minute) < 2 SELECT @minute = '0' + @minute
select @LastRestoredTranFile = @dbname + 'Log' +
convert(varchar(20),@LastSaveDate ,112) + '_' + @hour + @minute
end
end
-- Find all Log Backup newer than the one restored to be applied
select 'Last Log Restore : ' + @LastRestoredTranFile
declare @CurrentTranFileName varchar(255)
create table #Log(TranFileName varchar(255))
select @Command = 'DIR ' + @LocalBackupDir + '' + @dbname + 'Log' +
'*.DMP
/ON /B'
insert #Log exec master..xp_cmdshell @Command
declare c cursor for
SELECT * from #Log where TranFileName > @LastRestoredTranFile
open c
fetch c into @CurrentTranFileName
while @@fetch_status =0
begin
-- Restore Tran in order
select 'Current Log Restore : ' + @CurrentTranFileName
select @FullTranFileName = @LocalBackupDir + '' + @CurrentTranFileName
LOAD TRANSACTION @Localdbname FROM DISK = @FullTranFileName WITH STATS > 100
-- Rename file to avoid further duplicate restores
SELECT @Command = 'RENAME ' + @LocalBackupDir + '' +
@CurrentTranFileName
+ ' ' +
+ '_' + @CurrentTranFileName
--print @command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Erreur au renommage du fichier de sauvegarde du Log',16,-1)
end
fetch c into @CurrentTranFileName
end
close c
deallocate c
drop table #Log
go


--dump database pubs to pubs with init
--insert t1 select * from t1
--ExpSaveLog 'Pubs' , 'C:MSSQL7BACKUP'

--LOAD database pubs2 from pubs
--insert t1 select * from t1
--ExpSaveLog 'Pubs2' , 'C:MSSQL7BACKUP'


DROP PROCEDURE EXPKillProcessOnBackup
go
CREATE PROCEDURE EXPKillProcessOnBackup @dbname varchar(30)='Saretec' AS
SET NOCOUNT ON
DECLARE @vc_spid varchar(20)
DECLARE Cursor_ProcessOnCandidate
INSENSITIVE
CURSOR FOR
SELECT CONVERT(varchar(20),spid)
FROM master..sysprocesses
WHERE dbid= DB_ID(@dbname)
OPEN Cursor_ProcessOnCandidate
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM Cursor_ProcessOnCandidate
INTO @vc_spid
IF (@@fetch_status <> 0)
BEGIN
DEALLOCATE Cursor_ProcessOnCandidate
BREAK
END
EXEC ('KILL ' + @vc_spid)
END
go

DROP PROCEDURE EXPPurgeLogPrim
go
CREATE PROCEDURE EXPPurgeLogPrim
@dbname varchar(30) = 'Saretec' ,
@BackupDir varchar(255) = 'D:MSSQLBACKUP'
AS
DECLARE @retcode int ,
@Command varchar(255)
-- Purge des fichiers de sauvegarde (<Base>LogYYYYMMDD_HHMM.DMP &
<Base>LogYYYYMMDD_HHMM.CAB)
SELECT @Command = 'DEL ' + @BackupDir + '' + @dbname +'Log' + '*.CAB'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting CAB Compressed Log Backup File',16,-1)
return (1)
end
SELECT @Command = 'DEL ' + @BackupDir + '' + @dbname +'Log' + '*.DMP'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting Transaction Log Backup File',16,-1)
return (1)
end
GO

DROP PROCEDURE EXPPurgeLogBack
go
CREATE PROCEDURE EXPPurgeLogBack
@dbname varchar(30) = 'Saretec' ,
@BackupDir varchar(255) = 'D:MSSQLBACKUP' ,
@CABBackupDir varchar(255) = 'D:MSSQLBACKUPCAB'
AS
DECLARE @retcode int ,
@Command varchar(255)
-- Purge des fichiers compress, historis,s (<Base>LogYYYYMMDD_HHMM.CAB)
SELECT @Command = 'DEL ' + @CABBackupDir + '' + @dbname +'Log' + '*.CAB'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting CAB Compressed Log Backup File',16,-1)
return (1)
end
-- Purge des fichiers Log restaur,s historis,s
(_<Base>LogYYYYMMDD_HHMM.DMP)
SELECT @Command = 'DEL ' + @BackupDir + '_' + @dbname +'Log' + '*.DMP'
print @Command
EXEC @retcode = master..xp_cmdshell @Command
IF @retcode <> 0
begin
raiserror('Error deleting Transaction Log Backup File',16,-1)
return (1)
end
GO

Phil.


"M'bark BOULOUIRD" <bark.news@DELETEspam.logaviv.com> wrote in message
news:#xRbagv0EHA.1188@tk2msftngp13.phx.gbl...

bonjour,

sous SQL server 2000 standard sp3

j'effectue automatiquement des sauvegardes du Journal des transacation

avec

truncate, j'ajoute les backup au fur et a mesure sur le meme device

BACKUP LOG [logship] TO [logship_log_backup_device] WITH NOINIT ,

NOUNLOAD

, NAME = N'logship backup', NOSKIP , STATS = 10, NOFORMAT

mais voila je souhaiterais creer un script qui me restaure tous mes
backup
de JT au moment voulu. ( j'ai trois backup dans mon device , je voudrais
qu'il me joue les troi backup dans l'ordre).


comment faire pour automatiser la tache( sans entreprise manager)?

merci d'avance.

M'bark

Bonjour,

C'est essentiellement la procédure EXPRestoreLog qui vous intéressait mais
je me suis dit que le package complet pouvait avoir un intéret certain !!!

Bon courage.

Phil.

"M'bark BOULOUIRD" <bark.news@DELETEspam.logaviv.com> wrote in message
news:OEpWtqv0EHA.2540@TK2MSFTNGP09.phx.gbl...

ahhh oui merci, j'ai bien du boulot

mais merci beaucoup c tres gentil,

je vais me plonger dans la lecture de votre code SQL

merci

M'bark


"Philippe [MS]" <ptrotin@online.microsoft.com> a écrit dans le message de
news: OWmXejv0EHA.1860@TK2MSFTNGP15.phx.gbl...
> Bonjour,
>
> Quelque chose comme ça ???
>
>
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
> DROP PROCEDURE EXPSaveLog
> go
> CREATE PROCEDURE EXPSaveLog
> @dbname varchar(30) = 'Saretec' , @BackupDir varchar(255) > > 'S:MSSQLBACKUP' ,
> @TransactionFile varchar(255) OUTPUT AS
> SET NOCOUNT ON
> DECLARE @FullTransactionFile varchar(255)
> DECLARE @hour varchar(2)
> DECLARE @minute varchar(2)
> SELECT @hour = DATENAME(hour,getdate())
> IF DATALENGTH(@hour) < 2 SELECT @hour = '0' + @hour
> SELECT @minute = DATENAME(minute,getdate())
> IF DATALENGTH(@minute) < 2 SELECT @minute = '0' + @minute
> SELECT @TransactionFile = @dbname + 'Log'
> + LTRIM(convert(varchar(30),getdate(),112)) -- Format ISO YYYYMMDD
> + '_' + @hour + @minute
> + '.DMP'
> SELECT @FullTransactionFile = @BackupDir + '' + @TransactionFile
> DUMP TRANSACTION @dbname TO DISK = @FullTransactionFile WITH STATS 100,
> INIT
> go
> DROP PROCEDURE EXPCopyLog
> go
>
> CREATE PROCEDURE EXPCopyLog
> @dbname varchar(30)='Saretec' , @TransactionFile varchar(255) ,
> @PrimaryServer varchar(30) ='C900SE15' , @PrimShareName varchar(255) > > 'S$'
> , @RemBackupDir varchar(255) = 'MSSQLBACKUP' ,
> @BackupServer varchar(30) ='X040SS03' , @BackShareName varchar(255) 'D$'
> , @LocalBackupDir varchar(255) = 'MSSQLBACKUP' ,
> @compress int = 0 AS
> DECLARE @retcode int ,
> @Command varchar(255) ,
> @PrimaryTransactionFile varchar(255) ,
> @FileExtension varchar(3) ,
> @FileWithoutExtension varchar(255) ,
> @CompTransactionFile varchar(255) ,
> @ExePath varchar(255) ,
> @SourcePath varchar(255)
>
> select @FileExtension =RIGHT(@TransactionFile ,
> datalength(@TransactionFile) - CHARINDEX('.', @TransactionFile ))
> select @FileWithoutExtension =substring(@TransactionFile , 1,
> CHARINDEX('.',
> @TransactionFile )-1)
> if @compress = 1
> begin
> select @CompTransactionFile = @FileWithoutExtension + '.CAB'
> select @ExePath ='C:Tempmcs' -- change your path Here !!!
> select @SourcePath = '\' + @PrimaryServer + '' + @PrimShareName + ''

+

> @RemBackupDir
> SELECT @Command = @ExePath + 'MAKECAB.EXE /L ' + @SourcePath + ' ' +
> @SourcePath + '' + @TransactionFile + ' ' + @CompTransactionFile
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error compressing Transaction Log Backup File',16,-1)
> return (1)
> end
> select @TransactionFile = @CompTransactionFile
> end
> SELECT @PrimaryTransactionFile = '\' + @PrimaryServer + '' +
> @PrimShareName
> + '' + @RemBackupDir + '' + @TransactionFile
> SELECT @TransactionFile = '\' + @BackupServer + '' +
> @BackShareName
> + '' + @LocalBackupDir + '' + @TransactionFile
> --SELECT @Command = 'del ' + @TransactionFile
> --EXEC @retcode = master..xp_cmdshell @Command
> --IF @retcode <> 0
> --begin
> -- raiserror('Error deleting Transaction Log Backup File',16,-1)
> -- return (1)
> --end
> SELECT @Command = 'copy ' + @PrimaryTransactionFile + ' ' +
> @TransactionFile
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error copying Transaction Log Backup File',16,-1)
> return (1)
> end
>
> go
>
> DROP PROCEDURE EXPExtractLog
> go
>
> CREATE PROCEDURE EXPExtractLog
> @CABBackupDir varchar(255) = 'D:MSSQLBACKUP' ,
> @LogBackupDir varchar(255) = 'D:MSSQLBACKUP' ,
> @CABCopyDir varchar(255) = 'D:MSSQLBACKUPCAB'
> AS
> DECLARE @retcode int ,
> @Command varchar(255) ,
> @CurrentCABFileName varchar(255),
> @FileExtension varchar(3) ,
> @ExePath varchar(255)
>
> select @FileExtension = 'CAB'
> select @ExePath ='C:WINNT' -- change your path Here !!!
> -- Find all CAB file in the destination CAB directory
> create table #CAB(CABFileName varchar(255))
> select @Command = 'DIR ' + @CABBackupDir + '*.' + @FileExtension + '

/ON

> /B'
> insert #CAB exec master..xp_cmdshell @Command
> declare c cursor for
> SELECT * from #CAB
> open c
> fetch c into @CurrentCABFileName
> while @@fetch_status =0
> begin
> -- Extract CAB file
> if @CurrentCABFileName like '%Fichier introuvable%' break -- Output de
> DIR si no match
> SELECT @Command = @ExePath + 'extract.EXE /L ' + @LogBackupDir + ' /E

/Y

> '
> + @CABBackupDir + '' + @CurrentCABFileName
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error Uncompressing file',16,-1)
> break -- process next CAB file
> end
> -- Move CAB file to a copy directory waiting for purge
> SELECT @Command = 'MOVE ' + @CABBackupDir + '' + @CurrentCABFileName +

'

> ' + @CABCopyDir
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error Moving CAB file',16,-1)
> break -- process next CAB file
> end
> fetch c into @CurrentCABFileName
> end
> close c
> deallocate c
> go
>
>
> DROP PROCEDURE EXPRestoreLog
> go
> CREATE PROCEDURE EXPRestoreLog
> @dbname varchar(30)='Saretec' , @LocalBackupDir varchar(255) > > 'D:MSSQLBACKUP' ,
> @Localdbname varchar(30)='Saretec' , @compress int = 0 AS
> SET NOCOUNT ON
> DECLARE @hour varchar(2) ,
> @minute varchar(2) ,
> @TransactionFile varchar(255) ,
> @FullTranFileName varchar(255) ,
> @PrimaryTransactionFile varchar(255) ,
> @LastRestoredTranFile varchar(255) ,
> @LastRestoredDBFile varchar(255) ,
> @retcode int ,
> @Command varchar(255) ,
> @LastRestoredDate datetime ,
> @LastSaveDate datetime
>
> -- Get the last Restored Log file from msdb history restore tables (6.5
> only)
> -- Take last TranFile restored after last Full DB restore
> SELECT @LastRestoredTranFile = max(device_name) from
> msdb..sysrestorehistory
> h , msdb..sysrestoredetail d
> where h.restore_id = d.restore_id and source_database_name @Localdbname
> and backup_type = 2
> and restore_date >
> (select max(restore_date) from msdb..sysrestorehistory
> where source_database_name = @Localdbname and backup_type = 1)
> -- Disk volume has full pathname , get relative pathname from
> @LocalBackupDir
> declare @i int
> select @i = datalength(@LastRestoredTranFile )
> select @i = @i - datalength(@LocalBackupDir) -1
> select @LastRestoredTranFile = right(@LastRestoredTranFile , @i)
> -- Si Null : soit initialisation , soit d,marrer au premier LogBackup
> suivant le dernier DBBackup
> if @LastRestoredTranFile is null
> begin
> SELECT @LastRestoredDBFile = device_name from msdb..sysrestorehistory h

,

> msdb..sysrestoredetail d
> where h.restore_id = d.restore_id and source_database_name > > @Localdbname and backup_type = 1
> and restore_date = (select max(restore_date) from
> msdb..sysrestorehistory
> where source_database_name = @Localdbname and backup_type = 1 )
> select 'Last Full DB Restore : ' + @LastRestoredDBFile
> if @LastRestoredDBFile is null
> begin
> raiserror('Il n''y a pas eu de restauration complSte de la base avant
> restauration des Log',16,-1)
> return 1
> end
> else
> begin
> SELECT @LastSaveDate = max(backup_start) from msdb..sysrestorehistory h
> where source_database_name = @Localdbname and backup_type = 1
> SELECT @hour = DATENAME(hour, @LastSaveDate )
> IF DATALENGTH(@hour) < 2 SELECT @hour = '0' + @hour
> SELECT @minute = DATENAME(minute, @LastSaveDate )
> IF DATALENGTH(@minute) < 2 SELECT @minute = '0' + @minute
> select @LastRestoredTranFile = @dbname + 'Log' +
> convert(varchar(20),@LastSaveDate ,112) + '_' + @hour + @minute
> end
> end
> -- Find all Log Backup newer than the one restored to be applied
> select 'Last Log Restore : ' + @LastRestoredTranFile
> declare @CurrentTranFileName varchar(255)
> create table #Log(TranFileName varchar(255))
> select @Command = 'DIR ' + @LocalBackupDir + '' + @dbname + 'Log' +
> '*.DMP
> /ON /B'
> insert #Log exec master..xp_cmdshell @Command
> declare c cursor for
> SELECT * from #Log where TranFileName > @LastRestoredTranFile
> open c
> fetch c into @CurrentTranFileName
> while @@fetch_status =0
> begin
> -- Restore Tran in order
> select 'Current Log Restore : ' + @CurrentTranFileName
> select @FullTranFileName = @LocalBackupDir + '' + @CurrentTranFileName
> LOAD TRANSACTION @Localdbname FROM DISK = @FullTranFileName WITH STATS

> > 100

> -- Rename file to avoid further duplicate restores
> SELECT @Command = 'RENAME ' + @LocalBackupDir + '' +
> @CurrentTranFileName
> + ' ' +
> + '_' + @CurrentTranFileName
> --print @command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Erreur au renommage du fichier de sauvegarde du

Log',16,-1)

> end
> fetch c into @CurrentTranFileName
> end
> close c
> deallocate c
> drop table #Log
> go
>
>
> --dump database pubs to pubs with init
> --insert t1 select * from t1
> --ExpSaveLog 'Pubs' , 'C:MSSQL7BACKUP'
>
> --LOAD database pubs2 from pubs
> --insert t1 select * from t1
> --ExpSaveLog 'Pubs2' , 'C:MSSQL7BACKUP'
>
>
> DROP PROCEDURE EXPKillProcessOnBackup
> go
> CREATE PROCEDURE EXPKillProcessOnBackup @dbname varchar(30)='Saretec' AS
> SET NOCOUNT ON
> DECLARE @vc_spid varchar(20)
> DECLARE Cursor_ProcessOnCandidate
> INSENSITIVE
> CURSOR FOR
> SELECT CONVERT(varchar(20),spid)
> FROM master..sysprocesses
> WHERE dbid= DB_ID(@dbname)
> OPEN Cursor_ProcessOnCandidate
> WHILE (1 = 1)
> BEGIN
> FETCH NEXT FROM Cursor_ProcessOnCandidate
> INTO @vc_spid
> IF (@@fetch_status <> 0)
> BEGIN
> DEALLOCATE Cursor_ProcessOnCandidate
> BREAK
> END
> EXEC ('KILL ' + @vc_spid)
> END
> go
>
> DROP PROCEDURE EXPPurgeLogPrim
> go
> CREATE PROCEDURE EXPPurgeLogPrim
> @dbname varchar(30) = 'Saretec' ,
> @BackupDir varchar(255) = 'D:MSSQLBACKUP'
> AS
> DECLARE @retcode int ,
> @Command varchar(255)
> -- Purge des fichiers de sauvegarde (<Base>LogYYYYMMDD_HHMM.DMP &
> <Base>LogYYYYMMDD_HHMM.CAB)
> SELECT @Command = 'DEL ' + @BackupDir + '' + @dbname +'Log' + '*.CAB'
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error deleting CAB Compressed Log Backup File',16,-1)
> return (1)
> end
> SELECT @Command = 'DEL ' + @BackupDir + '' + @dbname +'Log' + '*.DMP'
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error deleting Transaction Log Backup File',16,-1)
> return (1)
> end
> GO
>
> DROP PROCEDURE EXPPurgeLogBack
> go
> CREATE PROCEDURE EXPPurgeLogBack
> @dbname varchar(30) = 'Saretec' ,
> @BackupDir varchar(255) = 'D:MSSQLBACKUP' ,
> @CABBackupDir varchar(255) = 'D:MSSQLBACKUPCAB'
> AS
> DECLARE @retcode int ,
> @Command varchar(255)
> -- Purge des fichiers compress, historis,s (<Base>LogYYYYMMDD_HHMM.CAB)
> SELECT @Command = 'DEL ' + @CABBackupDir + '' + @dbname +'Log' +

'*.CAB'

> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error deleting CAB Compressed Log Backup File',16,-1)
> return (1)
> end
> -- Purge des fichiers Log restaur,s historis,s
> (_<Base>LogYYYYMMDD_HHMM.DMP)
> SELECT @Command = 'DEL ' + @BackupDir + '_' + @dbname +'Log' + '*.DMP'
> print @Command
> EXEC @retcode = master..xp_cmdshell @Command
> IF @retcode <> 0
> begin
> raiserror('Error deleting Transaction Log Backup File',16,-1)
> return (1)
> end
> GO
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>
> Phil.
>
>
> "M'bark BOULOUIRD" <bark.news@DELETEspam.logaviv.com> wrote in message
> news:#xRbagv0EHA.1188@tk2msftngp13.phx.gbl...
>> bonjour,
>>
>> sous SQL server 2000 standard sp3
>>
>> j'effectue automatiquement des sauvegardes du Journal des transacation
> avec
>> truncate, j'ajoute les backup au fur et a mesure sur le meme device
>>
>> BACKUP LOG [logship] TO [logship_log_backup_device] WITH NOINIT ,
> NOUNLOAD
>> , NAME = N'logship backup', NOSKIP , STATS = 10, NOFORMAT
>>
>> mais voila je souhaiterais creer un script qui me restaure tous mes
>> backup
>> de JT au moment voulu. ( j'ai trois backup dans mon device , je

voudrais

>> qu'il me joue les troi backup dans l'ordre).
>>
>>
>> comment faire pour automatiser la tache( sans entreprise manager)?
>>
>> merci d'avance.
>>
>> M'bark
>>
>>
>>
>
>