March 14, 2005 at 11:48 am
I have a trans log backup that appends it's backup to the same file on the hour. When I restore the database using this single file that contains 7 log backups in it will it automatically restore all the logs or do I have to specify which ones? I did a practice restore earlier when it had two backups within the file and it restored with no problem. Are there any potential pitfalls with this backup method? Should I have 7 different files one for each backup? or keep it the same? TIA.
March 14, 2005 at 1:25 pm
What you're describing sounds like a logical backup device with the backup set appended each time a log backup runs. I believe you have to specify which backup set you want to restore from the device. At least in Enterprise Manager, Restore defaults to the first file in the device.
The drawback to appending backups to a device is if the backup device or physical file that the device is assigned to is damaged, you lose a series of backups, not just one. That said, we append to backup devices in my shop and have not lost any yet.
Greg
Greg
March 15, 2005 at 5:19 am
when are your full backups been taken ?
I would recommend having separate T-Log backup files. Since you have 1 per hour, you could have 24 in a day, depending on when your Full backups are taken.
YOu could also delete your old T-log backup files, if you have Full backups taken at a interval of 12 hours in a day, or something like that.
--Kishore
March 15, 2005 at 6:16 am
I keep all my trans logs in one file. The risk of damage to the file can be overstated, because if you were to have 7 seperate files, any of those could get damaged, and damage to any one would render the subsequent files useless.
The file with the full b/u and the days transaction logs are backed up to tape every night. The first transaction log b/u of the day wipes the previous days entries. I have found this system to be very solid. I have not had a problem with any test restores nor my one actual restore.
I have noticed that using the EP to restore can have SQL Server understand you want all restored, and that's pretty nifty. I still tend to use the QA, in which case I have to specify the NO RECOVERY option until I post the last transaction log.
March 15, 2005 at 6:58 am
I backup to three different servers: 1 Full and 7 Trans Logs for 8 hours total to one server then the next two servers have follow the same setup for 24 hours worth of backups.
RonKyle how do you specify what Trans Log you want when they are all in one file. I did a practice restore yesterday from QA and set the NO RECOVERY option after the RESTORE DATABASE but when you RESTORE LOG it's only one file you specify, does it restore all Logs contained within??
After reading in depth yesterday I wanted to wipe out my previous days logs too but I am not sure how to go about it. I considered having the first trans log step backup with INIT and then the other steps with NO INIT. Not sure how to write 7 steps each backing up on a different hour. (Was thinking I will need two separate Jobs one for the NO INIT and one for the WITH INIT)
Thanks for your responses much appreciated!
March 15, 2005 at 7:36 am
My app isn't quite as intensive as yours, so you will need to make a few adjustments. I make 5 log b/u's during the use cycle, and one as part of the full b/u (the latter I found necessary to ensure that my tests always worked). The first 5 b/u's are at 10 am, 2, 6, and 10 pm. Here's the TSQL for the step I call from a job:
IF DATEPART(hh, GETDATE())=10 BEGIN
BACKUP LOG [db<Name>] TO
WITH INIT, NAME=N'db<Name> Backup L'
END
ELSE
BACKUP LOG [db<Name>] TO
WITH NOINIT, NAME=N'db<Name> Backup L'
END
As for identifying the file to restore, I need a separate Restore statement for the full b/u and each t/l. The first has FILE=1, the second FILE=2 and so one. I have WITH NORECOVERY written for all but the last. I have to remember to change that if I'm not restoring all of them.
Hope this helps.
March 15, 2005 at 10:12 am
Hi,
you can do the restore of all files in your container by using somthing similar to the following procedure:
/* **********************************************************************************
Procedure to restore TA-Logs into the Database
Params: name of the file containing the TA-log(s)
timelag in minutes for the recovery-database
name of the database to restore into
author: Karl Klingler
date: 22. April 2004
********************************************************************************** */
CREATE PROCEDURE restore_log_backups
@backupfilepath sysname,
@DBN sysname,
@stop int
AS
-- declare variables
declare @file smallint
declare @lsn decimal(38,0)
declare @minlsn decimal(38,0)
declare @maxlsn decimal(38,0)
declare @stopat sysname
declare @msg nvarchar(2000)
declare @cmd nvarchar(2000)
DECLARE @oldfile int
declare @backupstartdate datetime
declare @hexlsn nvarchar(22)
-- set defaults
select @lsn = 0
select @minlsn = 0
select @maxlsn = 0
select @oldfile = 0
SELECT @msg = '--- Beginning to restore transaction logs...'
RAISERROR(@msg,10,1) WITH NOWAIT
-- convert timelag in minutes to datetime
select @stopat= dateadd(mi,-@stop,getdate())
-- create temporary tables
--drop table #dblog1
--drop table #backupfile_header
select top 0 * into #dblog1 from ::fn_dblog( default, default )
CREATE TABLE #backupfile_header
(
BackupName nvarchar(128), BackupDescription nvarchar(255), BackupType smallint, ExpirationDate datetime, Compressed tinyint, Position smallint, DeviceType tinyint,
UserName nvarchar(128), ServerName nvarchar(128), DatabaseName nvarchar(128), DatabaseVersion int, DatabaseCreationDate datetime, BackupSize numeric(20,0),
FirstLSN numeric(25,0), LastLSN numeric(25,0), CheckpointLSN numeric(25,0), DatabaseBackupLSN numeric(25,0), BackupStartDate datetime, BackupFinishDate datetime,
SortOrder smallint, CodePage smallint, UnicodeLocaleId int, UnicodeComparisonStyle int, CompatibilityLevel tinyint, SoftwareVendorId int, SoftwareVersionMajor int,
SoftwareVersionMinor int, SoftwareVersionBuild int, MachineName nvarchar(128), Flags int, BindingID uniqueidentifier, RecoveryForkID uniqueidentifier,Collation nvarchar(128)
)
WAITFOR DELAY '00:00:05'
-- get headerinfo from backupfile into table #backupfile_header
insert #backupfile_header exec ('restore headeronly from disk = ''' + @backupfilepath + ''' ' )
WAITFOR DELAY '00:00:05'
-- get current LSN of recovery-database
insert #dblog1 exec (' use '+@dbn+' select * from ::fn_dblog( default, default )')
select @hexlsn = (select min([Current LSN]) from #dblog1)
select @lsn = master.dbo.lsn2dec(@hexlsn)
SELECT @msg = 'Current LSN of database '+@dbn+' is ' + convert(nvarchar,@lsn) + '.'
RAISERROR(@msg,10,1) WITH NOWAIT
-- test if backupfile contains the needed TA-backup
select @minlsn = (select top 1 FirstLsn from #backupfile_header where BackupName = 'SITESQL' order by FirstLsn)
select @maxlsn = (select top 1 LastLsn from #backupfile_header where BackupName = 'SITESQL' order by LastLsn desc)
SELECT @msg = 'The current LSN of DB '+@dbn+' is ' + rtrim(convert(char,@lsn)) + ', in the backup file ' + @backupfilepath + ' are LSN''s from ' + rtrim(convert(char,@minlsn)) + ' to ' + rtrim(convert(char,@maxlsn)) + '!'
-- if not raise hell about it
if @lsn @maxlsn
RAISERROR ( @msg,10,1) with LOG, NOWAIT
else
RAISERROR(@msg,10,1) WITH NOWAIT
-- for all valid TA-backups in the backupfile: recover them, but only up until timelag
while not ((select Position from #backupfile_header where BackupName = 'SITESQL' and FirstLSN@lsn)) is NULL
begin
-- get current LSN of recovery-database
delete from #dblog1
insert #dblog1 exec (' use '+@dbn+' select * from ::fn_dblog( default, default )')
-- select @hexlsn = (select min([Current LSN]) from master.dbo.dblog where Operation = 'LOP_BEGIN_RECOVERY')
select @hexlsn = (select min([Current LSN]) from #dblog1)
select @lsn = master.dbo.lsn2dec(@hexlsn)
SELECT @msg = '' + CHAR(10) + CHAR(13) + 'Current LSN in datenbase '+@dbn+' is ' + convert(nvarchar,@lsn) + '.'
RAISERROR(@msg,10,1) WITH NOWAIT
-- get fileposition
select @file = (select Position from #backupfile_header where BackupName = 'SITESQL' and FirstLSN@lsn)
-- same position twice means problems, break
if @oldfile = @file begin
SELECT @msg = '--- Restore finished, we tried twice to restore the same file position!'
RAISERROR(@msg,10,1) WITH NOWAIT
break
end
-- get and print some info about current recovery
select @minlsn = (select FirstLsn from #backupfile_header where BackupName = 'SITESQL' and position = @file)
select @maxlsn = (select LastLsn from #backupfile_header where BackupName = 'SITESQL' and position = @file)
select @backupstartdate = (select BackupStartDate from #backupfile_header where BackupName = 'SITESQL' and position = @file)
SELECT @msg = 'Current backup file position is '+rtrim(convert(char,@file))+', first LSN is '+rtrim(convert(char,@minlsn)) + ', last LSN is ' + rtrim(convert(char,@maxlsn)) + '.'
RAISERROR(@msg,10,1) WITH NOWAIT
SELECT @msg = 'Stopat is "' + @stopat + '", BackupStartDate is "' + rtrim(convert(varchar,@backupstartdate)) + '".'
RAISERROR(@msg,10,1) WITH NOWAIT
if @backupstartdate > dateadd(mi,-@stop,getdate()) Begin
SELECT @msg = '--No need to restore this backup, it was created at "' + rtrim(convert(varchar,@backupstartdate)) + '", '
RAISERROR(@msg,10,1) WITH NOWAIT
SELECT @msg = 'the restore stops at "' + @stopat + '". Finishing restore...'
RAISERROR(@msg,10,1) WITH NOWAIT
break
end
-- kick all users out of the recovery-db
exec master..user_trennen @DBN
WAITFOR DELAY '00:00:05'
-- do the actual recovery
set @cmd = 'RESTORE LOG '+@dbn+' FROM DISK = ''' + @backupfilepath + '''
WITH DBO_ONLY, STANDBY = ''e:\undo_'+@dbn+' .ldf'', STOPAT = ''' + @stopat + ''', FILE = ' + convert(varchar,@file)
SELECT @msg = 'SQL-Command: "' + @cmd + '".'
RAISERROR(@msg,10,1) WITH NOWAIT
EXEC (@cmd)
WAITFOR DELAY '00:00:05'
-- memorize fileposition
select @oldfile = @file
end
drop table #dblog1
drop table #backupfile_header
-- now no more backups in this backupfile
SELECT @msg = 'No more transaction logs to restore out of the current backup file ' + @backupfilepath + '!. ' + convert(nvarchar,getdate())
RAISERROR(@msg,10,1) WITH NOWAIT
Print '--- Ending restore of transaction logs...'
RAISERROR(@msg,10,1) WITH NOWAIT
GO
/********************************************************************/
/* **********************************************************************************
Procedure to kick users out of database
Params: name of the database
author: Karl Klingler (actually got it from somewhere else...)
date: 22. April 2004
********************************************************************************** */
CREATE PROCEDURE user_trennen
@dbname sysname
AS
DECLARE @dbid int, @spid int, @execstr varchar(15), @waittime varchar(15), @final_chk int
--Getting the database_id for the specified database
SET @dbid = DB_ID(@dbname)
--Get the lowest spid
TryAgain:
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid)
WHILE @spid IS NOT NULL
BEGIN
--To avoid the KILL attempt on own connection
IF @spid @@SPID
BEGIN
--Killing the connection
SET @execstr = 'KILL ' + LTRIM(STR(@spid))
EXEC(@execstr)
END
--Get the spid higher than the last spid
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
END
SET @final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid = @dbid)
--New connections popped up, or killed connections aren't cleaned up yet, so try killing them again
IF (@final_chk > 1)
BEGIN
RAISERROR ('Killing users was not completely successful.', 16, 1)
GOTO TryAgain
END
/* ++++++++++++++++++++++++ END of Procedure user_trennen +++++++++++++++++ */
GO
regards karl
Best regards
karl
March 15, 2005 at 11:49 am
Ich kann Deutsch, aber die meisten andere Mitgleider koennen es wahrscheinlich nicht. Kannst du bitte die auf deutschen Anweisungen uebersetzen? Danke.
I can read German, but most of the other members probably can't. Can you please translate the instructions that are in German. Thanks.
March 16, 2005 at 3:08 am
Hi,
edited my prior post and added code of proc "user_trennen"...
Best regards
karl
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply