August 14, 2014 at 12:03 am
Comments posted to this topic are about the item Restore Gene : Automating SQL Server Database Restores
August 14, 2014 at 2:40 am
This looks excellent. Thanks so much. I can't wait to try it out.
Best wishes,
Phil Factor
August 14, 2014 at 3:09 am
Thanks Phil,
I've been following you on twitter for years, your articles on simple talk are an absolute benchmark in excellence so your enthusiasm is really appeciated 🙂
If you have any problems, please let me know.
Best wishes
Paul
August 14, 2014 at 2:42 pm
Hey Paul,
What happens in a disaster recovery scenario where you lose msdb? Are your restore scripts only going to be as current as the backup history in msdb? For example, what happens if you only back up msdb daily (say in the morning) and lose the whole server late in the day.
Thanks!!!
August 14, 2014 at 3:04 pm
Hi,
This solution isn't going to work if you lose msdb. To mitigate this, you could add a final step to any SQL Agent backup jobs and call sp_RestoreGene for the DB that has just been backed up. Outputting to a text file would create and save the restore scripts ready, before the disaster.
Regards
Paul
August 14, 2014 at 11:57 pm
I have tried the script. It's very useful. Thanks for sharing it.
August 15, 2014 at 1:03 am
Thanks Patrick, please let me know if you have any problems with it.
Best Wishes
Paul
August 30, 2014 at 6:20 am
Hi,
Just a quick note to advice getting the latest version of the stored procedure and PowerShell script.
Click here for the most current version of the ps_RestoreGene script. [/url]
Click here for the most recent version of the sp_RestoreGene stored procedure.[/url]
There are a few issues addressed in version 3.6 including:
1. The Write-Host PoSh command was not working properly in SQL Agent jobs, a new $ConsoleFeedback PoSh parameter can be set to 0 to suppress Write-Host.
2. There were issues when the database name contained a space or a full stop
3. Query timeout issues during long running restores
4. The PoSh 'Throw' command used when there are no new log backups has been changed to 'Exit'. IE, in Log Shipping mode when there are no new backups then exit.
5. Include Device Type 7 backups in msdb to support Amazon Web Service network drives.
These were all suggestions from people using it, most of the fixes were actually supplied by them.
Best wishes
Paul
September 5, 2014 at 2:38 pm
I've run out of enthusiasm for the project but someone found and fixed a problem related to Case Sensitive database collation. The master copy of the source has been loaded into a GitHub project - https://github.com/XML400/RestoreGene
October 24, 2014 at 5:10 am
Paul - This might sound daft, can you tell me if I should populate the xml file beforehand? I tried executing the PowerShell script to log ship a database, but all I see is a select running at the source and it times out eventually.
C:\sp_LogShippingLight\sp_LogShippingLight.ps1 `
-LogShippingInitialize "1" `
-DBname "dbname" `
-PrimaryServer "PRI_INST" `
-StandbyServer "SEC_INST" `
-FromFileFullUNC "\\Server\Backups" `
-RestoreLog "C:\LogShipping_Track.xml" `
-Log_Reference "Track LogShipping" `
-Standby "1"
October 24, 2014 at 5:20 am
No, the xml file is created and referenced the the powershell, you don't need to do anything to it. It sounds like you might have permission issues.
Regards
Paul
October 24, 2014 at 6:03 am
Thanks a bunch for replying quickly. I have made sure the login executing the ps script is sysadmin and has access to the directories. This is what I see
Name : LogData
LocalName : LogData
NamespaceURI :
Prefix :
NodeType : Element
ParentNode : #document
OwnerDocument : #document
IsEmpty : True
Attributes : {}
HasAttributes : False
SchemaInfo : System.Xml.XmlName
InnerXml :
InnerText :
NextSibling :
PreviousSibling :
Value :
ChildNodes : {}
FirstChild :
LastChild :
HasChildNodes : False
IsReadOnly : False
OuterXml : <LogData />
BaseURI :
-----------------------------------------
RestoreGene Batch Execution Starting
-----------------------------------------
Executing SQL Command - +
EXEC dbname.dbo.sp_RestoreGene
@LogShippingVariableDeclare = 0,
@Database = 'mydb',
@Log_Reference = 'Track LogShipping',
@TargetDatabase = 'mydb',
@FromFileFullUNC = '\\myserver\mydb\Full',
@FromFileDiffUNC = '\\myserver\mydb\Full',
@FromFileLogUNC= '\\myserver\mydb\Full',
@StopAt = '2014-10-24T07:42:33',
@StandbyMode = '1',
@WithReplace = '0',
@WithRecovery = '0',
@WithCHECKDB = '0'
Invoke-Sqlcmd : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
At C:\sp_LogShippingLight.ps1:267
char:42+ $LogShippingLight_Results = Invoke-SQLCmd <<<< -Query $LogShippingLight_EXEC -QueryTimeout 60 -Database "dbadb" -ServerInstance $PrimaryServer
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
-----------------------------------------
No new log file backups
And this is what runs on primary before it times out. Note* I modified the timeout period to a minute so that I don't have to wait too long. The sp runs twice (different spids, each time for a minute) before it times out.
<?query --
WITH CTE
(
database_name
,current_compatibility_level
,Last_LSN
,current_is_read_only
,current_state_desc
,current_recovery_model_desc
,has_backup_checksums
,backup_size
,[type]
,backupmediasetid
,family_sequence_number
,backupfinishdate
,physical_device_name
,position
)
AS
(
--------------------------------------------------------------
-- Full backup - Most current immediately before @LogShippingStartTime
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.last_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'D' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,x.backup_finish_date AS backupfinishdate
,mf.physical_device_name
,bs.position
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND Device_Type IN (7,102,2)
AND a.is_copy_only = 0
AND a.backup_finish_date <= @LogShippingStartTime
GROUP BY database_name
) x
ON x.database_name = bs.database_name
AND x.backup_finish_date = bs.backup_finish_date
JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number
WHERE bs.type = 'D'
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
UNION
--------------------------------------------------------------
-- Differential backup, most current immediately before @StopAt
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.last_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'I' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,x.backup_finish_date AS backupfinishdate
,mf.physical_device_name
,bs.position
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN -- Last Diff before STOPAT
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'I'
AND Device_Type IN (7,102,2)
AND a.is_copy_only = 0
AND a.backup_finish_date <= ISNULL(@StopAt,GETDATE())
GROUP BY database_name
) x
ON x.database_name = bs.database_name
AND x.backup_finish_date = bs.backup_finish_date
INNER JOIN -- Last Full before STOPAT
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND Device_Type IN (7,102,2)
AND a.is_copy_only = 0
AND a.backup_finish_date <= @LogShippingStartTime
GROUP BY database_name
) y
ON y.database_name = bs.database_name
INNER JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number
WHERE bs.type = 'I'
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND x.backup_finish_date > y.backup_finish_date
AND @StopAt = @LogShippingStartTime
UNION
--------------------------------------------------------------
-- Log file backups - after 1st full & diff backups / before @STOPAT
SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.last_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'L' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,bs.backup_finish_date as backupfinishdate
,mf.physical_device_name
,bs.position
FROM msdb.dbo.backupset bs
INNER JOIN sys.databases d
ON bs.database_name = d.name
INNER JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number
INNER JOIN -- After the selected full backup
(
SELECT
database_name
,MAX(Last_LSN) full_backup_Last_LSN
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND Device_Type IN (7,102,2)
AND a.is_copy_only = 0
AND a.backup_finish_date <= @LogShippingStartTime
GROUP BY database_name
) y
ON bs.database_name = y.Database_name
LEFT OUTER JOIN -- After the selected differential backup
(
SELECT
database_name
,MAX(Last_LSN) diff_backup_Last_LSN
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'I'
AND Device_Type IN (7,102,2)
AND a.is_copy_only = 0
AND a.backup_finish_date < CASE @LogShippingStartTime WHEN @StopAt THEN ISNULL(@StopAt,'1 Jan, 1900') ELSE '1 Jan, 1900' END
GROUP BY database_name
) z
ON bs.database_name = z.database_name
LEFT OUTER JOIN -- Select the first log file after STOPAT
(
SELECT
database_name
,MIN(backup_finish_date) last_Log_After_StopAt
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'L'
AND Device_Type IN (7,102,2)
AND a.backup_finish_date > ISNULL(@StopAt,'1 Jan, 1900')
GROUP BY database_name
) x
ON bs.database_name = x.database_name
WHERE bs.backup_finish_date <= ISNULL(x.last_Log_After_StopAt,'31 Dec, 2050') -- Include 1st log after stop at
AND bs.Last_LSN > y.full_backup_Last_LSN -- After last full backup lsn
AND bs.Last_LSN > ISNULL(z.diff_backup_Last_LSN,'0') -- After last diff backup lsn if exists
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND bs.type = 'L'
AND Device_Type IN (7,102,2)
)
SELECT * INTO #CTE FROM CTE;
--------------------------------------------------------------
-- CTE2 Optionally, striped backup file details
--------------------------------------------------------------
--?>
Thanks again.
October 24, 2014 at 6:41 am
Could you try the latest versions - paulbrewer.wordpress.com
There is a change to the timeout setting, works much better.
Thanks
October 26, 2015 at 7:54 am
Hi,
I am taking backups via Idera SQL safe.
Will this work in that case and give the restore commands back in a format that Idera can understand?
October 26, 2015 at 1:48 pm
Hi,
I'm not familiar with the Idera backup process to be honest but suspect they use volume shadow copy technology the same as DPM. You'll probably need to use their management server to perform restores.
Best wishes
Paul
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply