July 23, 2014 at 2:18 pm
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
Thanks!
P.S. We are very weak right on HA/DR so I'm working off of a single SQL Server instance per application.
July 29, 2014 at 11:38 am
Here is a starting point...I use this to restore databases to my DR server...
Good Luck
ALTER PROCEDURE[dbo].[P_RestoreDatabase_DBName]
AS
SET NOCOUNT ON
DECLARE@DBNameVARCHAR( 64 )='DBName'
DECLARE@DirNameVARCHAR( 256 )
DECLARE@FileNameVARCHAR( 256 ),
@FileDateDATETIME,
@MaxFileDateDATETIME,
@DBRestoreSizeBIGINT,
@DBRestoreLSNNUMERIC( 25, 0 )
DECLARE@SQLVARCHAR( 5000 )
SELECT@DirName='L:\' + @DBName + '\'
SELECT@MaxFileDate=ISNULL( (SELECTMAX( DBRestoreFileDate )
FROMSystems.dbo.DBRestore
WHEREDBName=@DBName
ANDDBRestoreFileType='D' ), '2000/01/01' )
CREATE TABLE#Temp(
ListingVARCHAR( 256 ),
FileDateDATETIME,
DepthINT,
IsFileINT )
INSERT INTO#Temp( Listing, Depth, IsFile )
EXEC master.sys.xp_dirtree @DirName, 0, 1;
UPDATE#Temp
SETFileDate=REPLACE( SUBSTRING( Listing, ( LEN( @DBName ) + 8 + 1 ), 10 ), '_', '-' ) + ' ' + SUBSTRING( Listing, ( LEN( @DBName ) + 19 + 1 ), 2 ) + ':' + SUBSTRING( Listing, ( LEN( @DBName ) + 21 + 1 ), 2 ) + ':' + SUBSTRING( Listing, ( LEN( @DBName ) + 23 + 1 ), 2 )
WHEREDepth=1
ANDIsFile=1
ANDRIGHT( Listing, 3 )='BAK'
SELECT@FileName=MAX( Listing ),
@FileDate=MAX( FileDate )
FROM#Temp
WHERERIGHT( Listing, 3 )='BAK'
ANDFileDate>@MaxFileDate
IF@FileNameISNULL
OR@FileDateISNULL
BEGIN
SELECT'Nothing to process'
DROP TABLE#Temp
RETURN
END
CREATE TABLE#Temp2(
LogicalNameVARCHAR( 128 ),
PhysicalNameVARCHAR( 260 ),
TypeCHAR( 1 ),
FileGroupNameVARCHAR( 128 ),
SizeNUMERIC( 20, 0 ),
MaxSizeNUMERIC( 20, 0 ),
FileIDBIGINT,
CreateLSNNUMERIC( 25, 0 ),
DropLSNNUMERIC( 25, 0 )NULL,
UniqueIDUNIQUEIDENTIFIER,
ReadOnlyLSNNUMERIC( 25, 0 )NULL,
ReadWriteLSNNUMERIC( 25, 0 )NULL,
BackupSizeInBytesBIGINT,
SourceBlockSizeINT,
FileGroupIDINT,
LogGroupGUIDUNIQUEIDENTIFIERNULL,
DifferentialBaseLSNNUMERIC( 25, 0 )NULL,
DifferentialBaseGUIDUNIQUEIDENTIFIER,
IsReadOnlyBIT,
IsPresentBIT,
TDEThumbprintVARBINARY( 32 ) )
SELECT@FileName=@DirName + @FileName
SELECT@SQL='RESTORE FILELISTONLY FROMDISK = ''' + @FileName + ''''
INSERT INTO#Temp2
EXEC( @SQL )
SELECT@DBRestoreSize=SUM( BackupSizeInBytes )
FROM#Temp2
WHEREType='D'
DROP TABLE#Temp
DROP TABLE#Temp2
CREATE TABLE#Temp3(
BackupNameNVARCHAR(128),
BackupDescriptionNVARCHAR(255),
BackupTypeSMALLINT,
ExpirationDateDATETIME,
CompressedBIT,
PositionSMALLINT,
DeviceTypeTINYINT,
UserNameNVARCHAR(128),
ServerNameNVARCHAR(128),
DatabaseNameNVARCHAR(128),
DatabaseVersionINT,
DatabaseCreationDateDATETIME,
BackupSizeNUMERIC(20,0),
FirstLSNNUMERIC(25,0),
LastLSNNUMERIC(25,0),
CheckpointLSNNUMERIC(25,0),
DatabaseBackupLSNNUMERIC(25,0),
BackupStartDateDATETIME,
BackupFinishDateDATETIME,
SortOrderSMALLINT,
CodePageSMALLINT,
UnicodeLocaleIdINT,
UnicodeComparisonStyleINT,
CompatibilityLevelTINYINT,
SoftwareVendorIdINT,
SoftwareVersionMajorINT,
SoftwareVersionMinorINT,
SoftwareVersionBuildINT,
MachineNameNVARCHAR(128),
FlagsINT,
BindingIDUNIQUEIDENTIFIER,
RecoveryForkIDUNIQUEIDENTIFIER,
CollationNVARCHAR(128),
FamilyGUIDUNIQUEIDENTIFIER,
HasBulkLoggedDataBIT,
IsSnapshotBIT,
IsReadOnlyBIT,
IsSingleUserBIT,
HasBackupChecksumsBIT,
IsDamagedBIT,
BeginsLogChainBIT,
HasIncompleteMetaDataBIT,
IsForceOfflineBIT,
IsCopyOnlyBIT,
FirstRecoveryForkIDUNIQUEIDENTIFIER,
ForkPointLSNNUMERIC(25,0),
RecoveryModelNVARCHAR(60),
DifferentialBaseLSNNUMERIC(25,0),
DifferentialBaseGUIDUNIQUEIDENTIFIER,
BackupTypeDescriptionNVARCHAR(60),
BackupSetGUIDUNIQUEIDENTIFIER,
CompressedBackupSizeBIGINT,
containmentTINYINT )
SELECT@SQL='RESTORE HEADERONLY FROM DISK = ''' + @FileName + ''''
INSERT INTO#Temp3
EXEC( @SQL )
SELECT@DBRestoreLSN=CheckpointLSN
FROM#Temp3
INSERT INTOSystems.dbo.DBRestore(
DBName,DBRestoreDir,DBRestoreFile,DBRestoreFileDate,DBRestoreFileType,DBRestoreSize,DBRestoreLSN )
SELECT@DBName,@DirName,@FileName,@FileDate,'D',@DBRestoreSize,@DBRestoreLSN
--Close all connections to database
EXECdbo.P_CloseAllConnections@DBName
SELECT@SQL='RESTORE DATABASE ' + @DBName + ' FROM DISK = ''' + @FileName + ''' WITH REPLACE, STANDBY = ''L:\DBName\StandBy\DBName.bak'', STATS = 10'
SELECT@SQL
EXEC( @SQL )
July 29, 2014 at 3:59 pm
Power shall is the best option to traverse the folder and generate the scripts.
in case if If you are using the 3rd party backup software then we don't require power shall scripts.
Thank you,
Regards
Hema.,
July 29, 2014 at 5:57 pm
Hema kumar-337490 (7/29/2014)
Power shall is the best option to traverse the folder and generate the scripts.
It's "PowerShell" and so far as it being the "best option", I'll say "It Depends". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2014 at 11:28 am
Thanks ajay 44186!!
July 30, 2014 at 11:34 am
There are about as many ways to accomplish what you need to do as there are members on this site.
A good start might be engaging an experienced consultant to help walk you through the finer questions of your needs.
A fresh set of eyes that have experience will often times be able to help you see clearly in a few conversations.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
July 30, 2014 at 11:18 pm
Why dont you create restore scripts immediately after backup.... and save for later use... ?
Cheers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply