July 15, 2008 at 12:50 am
Hi,
I am having the full backup of database ,and some 12 transaction logs .
i am not having diff backup
now i restored from full db backup,
now for applying T-logs is there any T-sql command to apply all these 12 T-logs in a single shot.
:hehe:[/size]
July 15, 2008 at 6:51 am
now for applying T-logs is there any T-sql command to apply all these 12 T-logs in a single shot.
Technically, you can't. However, you can write a series of T-SQL commands to do the same. I found this in SBO :w00t::
RESTORE LOG
FROM DISK= 'PATH TO T-LOG FILE BACKUP'
WITH NORECOVERY
GO
RESTORE LOG
FROM DISK= 'PATH TO T-2ND LOG FILE BACKUP'
WITH NORECOVERY
Keep going until you've restored all 12 Transaction Log Backups. On the 12th statement, change the WITH NORECOVERY to WITH RECOVERY
Regards, Irish
July 15, 2008 at 7:46 am
WITH NORECOVERY to WITH RECOVERY
For the above can some one explain what is their difference?
As for the last trans log to be restored we specify with recovery ,
why is that so??:)
July 15, 2008 at 7:49 am
Specifying WITH RECOVERY recovers the database and brings it online. Once recovered, no more log backups can be applied (which is why you only want it specified on the last)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 7:53 am
thanks Gail;)
July 15, 2008 at 2:09 pm
rinu philip (7/15/2008)
WITH NORECOVERY to WITH RECOVERYFor the above can some one explain what is their difference?
As for the last trans log to be restored we specify with recovery ,
why is that so??:)
"WITH RECOVERY" rolls back uncommitted transactions, so you cannot apply subsequent txn logs.
"WITH NORECOVERY" keeps uncommitted transactions open, ready to be "closed" (committed) when subsequent logs are applied.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 15, 2008 at 10:36 pm
thanks for the reply!!:) it was helpful for me to understand the recovery and no recovery options!!
July 16, 2008 at 1:17 am
For restoring log file in one shot you have to write the script which took the all the log files from the folder and restore it according to there dates created. you can restore it in this manner.
July 16, 2008 at 8:59 am
i cheat and get sql to write t backp script for me
here is my script to generate restorecommands
declare @dbname varchar(100)
set @dbname='rssystem'
select 'restore log '+@dbname+' from disk='''+bmf.physical_device_name+''' with norecovery' from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediafamily bmf on bs.media_set_id=bmf.media_set_id
where bs.database_name=@dbname and bs.type='L'
order by bmf.media_set_id asc
MVDBA
July 16, 2008 at 9:14 am
--This is the script which generates script to restore tlogs after last full backup.
declare @dbname sysname
set @dbname =' '
select case When s1.type = 'L' then 'restore log ['+s1.database_name+'] from disk = '''+ s2.physical_device_name +
''' with = ''norecovery'''
else '--Full backup command not extracted as it is not required.'
end as Command
from msdb..backupset s1 inner join msdb..backupmediafamily s2
on s1.media_set_id = s2.media_set_id
where s1.database_name =@dbname and s1.type in('D','L') --and not s1.type in('D')
--and s1.backup_start_date >= '2007-11-20 06:20:33.000'
and s1.backup_start_date >=(select max(backup_start_date) from msdb..backupset where database_name =@dbname and type ='D') order by s1.backup_start_date asc
July 16, 2008 at 9:46 am
Okay, all that automated scripting is great, but it assumes that you're msdb is fully intact and that the backups were made on the Server where the generation scripts are running.
In a recovery situation scripts like this would not get your inexperienced DBA too far.
On the same note, I admire the initiative to build something that will restore all of the t-logs from the last full backup.
Regards, Irish
July 16, 2008 at 10:06 am
hey no-one said itwas perfect - just a tool to make long winded jobs easier
anyway - many ways to skin a cat - if you don'thave msdb available and you have ALL of the files in a folder this code will als generate a list of commands
declare @dbname varchar(100)
set @dbname='mydatabase'
declare @path varchar(100)
set @path='\\servername\c$\sqlbackups\mydatabase'
declare @cmd varchar(1000)
set @cmd='dir '+@path
create table #temp(output varchar(2000))
insert into #temp exec xp_cmdshell @cmd
select 'restore log '+@dbname+' from disk='''+substring(output,charindex(@dbname,output),100)+''' with norecovery ' from #temp where output like '%.trn%' and output like '%'+@dbname+'%'
order by substring(output,charindex(@dbname,output),100) asc
drop table #temp
MVDBA
July 22, 2008 at 3:45 am
:P[font="Arial Black"][/font]
see there is no way to restore transaction logs in single T-sql command,
any how we have to first restore the fullbackup with no recovery:)
then apply all the t-logs with no recovery
finally last T-log should be applied wth Recovery option .:w00t:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply