May 12, 2009 at 2:57 am
1)What is the use of Log shipping transaction ?
2)Can we restore a corrupted database by using these log shipping transaction files?
3)Tell me the procedure hoe to restore a DB if we can restore a corrupted database by using these log shipping transaction files?
Thank you,
Venu Gopal.K
Software Engineer
INDIA
May 12, 2009 at 6:54 am
venu_ksheerasagaram (5/12/2009)
1)What is the use of Log shipping transaction ?
Log shipping creates a warm secondary server so that if anything happens to the primary server, apps can use the secondary. It's a form of High availability
2)Can we restore a corrupted database by using these log shipping transaction files?
Providing you have a full backup, yes, same as you would use log backups from a DB that isn't log shipped
3)Tell me the procedure hoe to restore a DB if we can restore a corrupted database by using these log shipping transaction files?
Restore the last full backup of the database
Restore all the transaction log backups, in order, up to and including the tail-log backup
Bring the DB online.
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
May 12, 2009 at 7:14 am
HI thank u for u r valuable solution.
Let me know how to Restore a DB using Log transaction files?
Give me a Practical situation and step by step procedure?
Thank's a lot,
Venu Gopal.K
Software Engineer
INDIA
May 12, 2009 at 7:25 am
venu_ksheerasagaram (5/12/2009)
Let me know how to Restore a DB using Log transaction files?
I just did.
Give me a Practical situation and step by step procedure?
Look in Books Online under RESTORE DATABASE. There's lots of script examples there.
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
May 12, 2009 at 7:41 am
Do all your restores using the NORECOVERY option, and then you can use an ALTER DATABASE to bring the database online. The reason is that if you don't use this, you can't do more restores of logs.
A common situation: You perform full backups at midnight, log backups every hour. You lose data somehow at 4:12pm. Do you want to return to the midnight backup and lose all data or do you want to restore to 4:00pm?
As Gail mentioned, there are script examples in Books Online. We are happy to help, but you need to do a little work yourself.
http://msdn.microsoft.com/en-us/library/ms190372(SQL.90).aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 12, 2009 at 7:45 am
venu_ksheerasagaram (5/12/2009)
3)Tell me the procedure hoe
Hey, watch your language!
-- You can't be late until you show up.
May 12, 2009 at 7:52 am
This is a little old[/url], but it ought to help.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 12, 2009 at 7:52 am
tosscrosby (5/12/2009)
Hey, watch your language!
I suspect that was nothing more than a mistype. "Tell me the procedure how to restore a DB"
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
May 12, 2009 at 7:55 am
Steve Jones - Editor (5/12/2009)
Do all your restores using the NORECOVERY option, and then you can use an ALTER DATABASE to bring the database online. The reason is that if you don't use this, you can't do more restores of logs.
Wouldn't you use RESTORE DATABASE to bring it from Restoring to Online after restoring all the backups? Can ALTER DATABASE bring a DB out of restoring?
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
May 12, 2009 at 8:02 am
GilaMonster (5/12/2009)
tosscrosby (5/12/2009)
Hey, watch your language!I suspect that was nothing more than a mistype. "Tell me the procedure how to restore a DB"
I know. Just having a relatively easy morning for a change and was trying to make light of the misspelling since I was provided with an early morning tongue-in-cheek chuckle.....:-P
Edit: I should've added a "winkie" to my first post!
-- You can't be late until you show up.
May 12, 2009 at 8:46 am
well, I thought that ALTER DATABASE SET ONLINE would work but it doesn't in 2005. Perhaps my memory is failing.
RESTORE DATABASE xxx WITH RECOVERY will bring it online.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 12, 2009 at 11:42 am
Here's something that might help:
use DBA;
go
create proc dbo.RestorePointInTime
(@DBName sysname,
@PointInTime datetime,
@TargetDBName sysname = null,
@MDFLocation nvarchar(128) = null,
@LDFLocation nvarchar(128) = null)
as
/*
Does not work with databases with more than one MDF or LDF.
Does not work without a source database (@DBName) that exists on
the current server.
*/
set nocount on;
if @DBName is null
begin
raiserror('Requires valid database name.', 16, 1);
return;
end;
if @DBName like '%[]]%' or @DBName like '%--%' or @DBName like '%;%'
begin
raiserror('Invalid character(s) in database name. Possible injection attack.', 16, 1)
return;
end
if not exists
(select *
from master.sys.databases
where name = @DBName)
begin
raiserror('Database does not exist on this server.', 16, 1)
return
end
if @TargetDBName is null
select @TargetDBName = @DBName;
declare @SQL nvarchar(max), @params nvarchar(100), @MDFName nvarchar(128), @LDFName nvarchar(128);
if @MDFLocation is null
begin
select @SQL = 'select @FileMDF = physical_name from [' + @DBName + '].sys.database_files where type = 0',
@params = '@FileMDF nvarchar(128) output';
exec sp_executesql @SQL, @params, @FileMDF = @MDFLocation output;
end;
if @LDFLocation is null
begin
select @SQL = 'select @FileLDF = physical_name from [' + @DBName + '].sys.database_files where type = 1',
@params = '@FileLDF nvarchar(128) output';
exec sp_executesql @SQL, @params, @FileLDF = @LDFLocation output;
end;
select @SQL = 'select @NameMDF = name from [' + @DBName + '].sys.database_files where type = 0',
@params = '@NameMDF nvarchar(128) output';
exec sp_executesql @SQL, @params, @NameMDF = @MDFName output;
select @SQL = 'select @NameLDF = name from [' + @DBName + '].sys.database_files where type = 1',
@params = '@NameLDF nvarchar(128) output';
exec sp_executesql @SQL, @params, @NameLDF = @LDFName output;
;with CTE as
(select physical_device_name as BackupLocation, backup_start_date as BkDate, row_number() over (order by backup_start_date desc) as Row
from msdb.dbo.backupmediafamily media
inner join msdb.dbo.backupset bkset
on media.media_set_id = bkset.media_set_id
where software_vendor_id = 4608
and type = 'D'
and database_name = @DBName
and backup_start_date <= @PointInTime)
select BackupLocation, BkDate, 'Full' as BkType
into #Backups
from CTE
where Row = 1;
if @@Rowcount = 0
begin
raiserror('No record in MSDB of necessary full backup file. Cannot run without that.', 16, 1);
return;
end;
alter table #Backups
add ID int identity primary key;
insert into #Backups (BackupLocation, BkDate, BkType)
select physical_device_name as BackupLocation, backup_start_date as BkDate, 'Tran'
from msdb.dbo.backupmediafamily media
inner join msdb.dbo.backupset bkset
on media.media_set_id = bkset.media_set_id
where software_vendor_id = 4608
and type = 'L'
and database_name = @DBName
and backup_start_date
(select max(BkDate)
from #Backups)
alter table #Backups
add Found bit not null default(0);
declare Files cursor local fast_forward for
select BackupLocation, Found
from #Backups;
declare @File nvarchar(128), @Found int;
open Files;
fetch next from Files
into @File, @Found;
while @@Fetch_Status = 0
begin
exec master.dbo.xp_fileexist @File, @Found output;
update #Backups
set Found = @Found
where BackupLocation = @File;
fetch next from Files
into @File, @Found;
end;
close Files;
deallocate Files;
declare @Msg nvarchar(256);
if exists
(select *
from #Backups
where Found = 0
and BkType = 'Full')
begin
select @Msg = 'Full Backup File Missing: ' + BackupLocation + '. Cannot do restore without preceding Full Backup.'
from #Backups
where BkType = 'Full'
and Found = 0;
raiserror(@Msg, 16, 1);
return;
end
if exists
(select *
from #Backups
where Found = 0
and BkType = 'Tran')
begin
select @Msg = coalesce(@Msg + '; ' + BackupLocation, BackupLocation)
from #Backups
where BkType = 'Tran'
and Found = 0;
select @Msg = 'Tran Log Backup File(s) Missing: ' + @Msg + '. Cannot do restore without complete chain of transaction log backups between preceding full and point-in-time.';
raiserror(@Msg, 16, 1);
return;
end
alter table #Backups
add Script varchar(1000);
update #Backups
set Script = 'restore database ' + @TargetDBName + '
from disk = ''' + BackupLocation + '''
with
replace,
norecovery,
move ''' + @MDFName + ''' to ''' + @MDFLocation + ''',
move ''' + @LDFName + ''' to ''' + @LDFLocation + ''';
'
where BkType = 'Full';
update #Backups
set Script = 'restore log ' + @TargetDBName + '
from disk = ''' + BackupLocation + '''
with
norecovery,
stopat = ''' + convert(nvarchar(100), @PointInTime, 109) + ''',
move ''' + @LDFName + ''' to ''' + @LDFLocation + ''';
'
where BkType = 'Tran';
select @SQL = null;
select @SQL = coalesce(@SQL + Script, Script)
from #Backups
order by BkDate;
select @SQL = @SQL + + 'restore database ' + @TargetDBName + '
with recovery;'
--print @SQL;
exec (@SQL);
select *
from #Backups;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2009 at 9:55 pm
Thank u for providing me the code above
Can u pls explain me what the code is going on? and what @sql value i hav to provide to execute?
Thank U,
Venu Gopal.K
Software Engineer
INDIA
May 12, 2009 at 10:22 pm
May I suggest that you spend some time reading Books Online, especially the sections on backup and restore?
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
May 13, 2009 at 6:55 am
venu_ksheerasagaram (5/12/2009)
Thank u for providing me the code aboveCan u pls explain me what the code is going on? and what @sql value i hav to provide to execute?
Thank U,
You don't provide the @SQL value. You provide the values for the input parameters, the proc does the rest of it for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply