July 22, 2004 at 3:51 am
Hi,
I'm working with a disaster recory server. Backup are automatically copied to DR server. On my DR server I create a SP to restore last backup. Now I restore always all DB. I want just restore the DB where the backup file is newer than the last backup date. I'm trying with the command "Restore with HEADERONLY" but I can not send this result to a variable.
Thanks in advance.
Jonathan
July 23, 2004 at 3:33 am
Hi, you could use something like:
-- create table
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
insert #backupfile_header exec ('restore headeronly from disk = '''+@path + @backupfilename + ''' ' )
WAITFOR DELAY '00:00:05'
.
.
.
-- get fileposition for wanted database
select @file = (select Position from #backupfile_header where ... )
-- restore database with moving files to new position
exec ('RESTORE DATABASE xxx
FROM DISK = ''yyy'' WITH
DBO_ONLY, REPLACE, STANDBY = ''\undo_'+@dbn+'.ldf'',
FILE = ' + @file )
WAITFOR DELAY '00:00:05'
DROP TABLE #backupfile_header
Best regards
karl
July 23, 2004 at 4:00 am
It's perfect, now I can finish my job.
Thanks for your help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply