Database Out of Sync
-
Hi All,
I have a Problem:- We have two databases that has been configures for Lgshipping.Logs from ServerA has been restored to ServerB.The destination Database on ServerB Is in Read Only Mode/Suspect as the Logs have not been restored for nearly 6-7 Hrs Now.
I have to bring both the databases in Sync.Is it Possible to take the Back Up from ServerA(full backup) and restore it on ServerB and during this restore if any tran Log Backup happens also restore for the same.
After applying the Restore will the 2 Databases be in Sync?
Say I wont be able to take the Back Up Of ServerA(Production) can i use the Previous Night BackUp and apply the Tran Log Backups after this restoring Full Backup of previous Night.
Thanks in Advance......
-
This should work automaticaly if you have setup logshipping.
Check BOl for "log shipping, overview"
Check your server's sqlagent if the jobs have been or are running ...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
you should be able to start the correct sqlagent job to invoke the replication cycle to get them back in sync.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
So, how does your logshipping work ?
Did you build you own backup\restore cyclic procedure ?
Maybe this script can help out.
It's meanth to generate a script to restore a sequence of logbackups.
-- JOBI dd 2004-09-21
-- generate restore log statements for files in a backupset
--
declare @Logbackup_Filename varchar(500)
declare @Restore_New_DBname varchar(128)
select @Logbackup_Filename = 'c:\dtestLog.bak'
, @Restore_New_DBname = 'DTEST1'
declare @RestoreDb_stmt varchar(5000)
select @RestoreDb_stmt = 'restore database ' + @Restore_New_DBname + '
FROM DISK = ''c:\dtestfull.bak''
WITH MOVE N''DTEST'' TO N''U:\MSSQL$ALBE0DB78\Data\' + @Restore_New_DBname + '.mdf''
, MOVE N''DTEST_log'' TO N''S:\MSSQL$ALBE0DB78\Log\' + @Restore_New_DBname + '_log.LDF''
, replace
, NoRecovery '
print @RestoreDb_stmt + char(13) + 'GO'
-- exec (@RestoreDb_stmt)
if (select isnull( object_id(N'tempdb.[dbo].[#Tmp_BackupHeaders]'), 0)) <> 0
begin
DROP table #Tmp_BackupHeaders
end
-- versie SQL2K sp3a
create table #Tmp_BackupHeaders (
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)
)
declare @sql varchar(5000)
set @sql = 'RESTORE HEADERONLY FROM DISK = ''' + @Logbackup_Filename + ''' '
insert into #Tmp_BackupHeaders
exec (@SQL)
declare csrRestoreLog cursor for
select 'print ''-- db [' + convert(varchar(128),DatabaseName) + '] -- ' + convert(varchar(15), Position)
+ ' -- LSN ' + convert(varchar(128), FirstLSN ) + ' <-> ' + convert(varchar(128), LastLSN )
+ ' -- BackupDate ' + convert(varchar(25), BackupStartDate, 121) + ' <--> ' + convert(varchar(25), BackupFinishDate, 121)
+ ' ''' + char(13)
+ 'Restore Log ' + @Restore_New_DBname + '
FROM DISK = ''' + @Logbackup_Filename + '''
WITH FILE = ' + convert(varchar(15),Position) + char(13)
+ case Position when M.MAX_Position then ' , Recovery '
else ' , NoRecovery '
end
-- + char(13) + 'GO'
from #Tmp_BackupHeaders
, (select max(Position) as MAX_Position from #Tmp_BackupHeaders ) M
order by Position
declare @RestoreLog_stmt varchar(5000)
open csrRestoreLog
FETCH NEXT FROM csrRestoreLog
INTO @RestoreLog_stmt
WHILE @@FETCH_STATUS = 0
BEGIN
print @RestoreLog_stmt + char(13) + 'GO'
-- exec (@RestoreLog_stmt)
-- Volgende rij inlezen
FETCH NEXT FROM csrRestoreLog
INTO @RestoreLog_stmt
END
-- Cursor afsluiten
CLOSE csrRestoreLog
DEALLOCATE csrRestoreLog
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Did you find a solution ?
I'll be out of the office until 01/25
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Try http://www.sqlscripter.com to sync your data. This tool can create
IF NOT EXISTS (...)
INSERT INTO (...)
ELSE
UPDATE (...)
T-SQL Scripts for you.
-Thomas
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply