Restore Gene : Automating SQL Server Database Restores

  • Paul Brewer

    SSCrazy

    Points: 2858

    Comments posted to this topic are about the item Restore Gene : Automating SQL Server Database Restores

  • Phil Factor

    SSC-Insane

    Points: 20115

    This looks excellent. Thanks so much. I can't wait to try it out.

    Best wishes,
    Phil Factor
    Simple Talk

  • Paul Brewer

    SSCrazy

    Points: 2858

    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

  • heb1014

    Hall of Fame

    Points: 3790

    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!!!

  • Paul Brewer

    SSCrazy

    Points: 2858

    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

  • Patrick Ge

    SSC Eights!

    Points: 912

    I have tried the script. It's very useful. Thanks for sharing it.

  • Paul Brewer

    SSCrazy

    Points: 2858

    Thanks Patrick, please let me know if you have any problems with it.

    Best Wishes

    Paul

  • Paul Brewer

    SSCrazy

    Points: 2858

    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

  • Paul Brewer

    SSCrazy

    Points: 2858

    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

  • arun.gopinath

    SSC Enthusiast

    Points: 161

    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"

  • Paul Brewer

    SSCrazy

    Points: 2858

    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

  • arun.gopinath

    SSC Enthusiast

    Points: 161

    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.

  • Paul Brewer

    SSCrazy

    Points: 2858

    Could you try the latest versions - paulbrewer.wordpress.com

    There is a change to the timeout setting, works much better.

    Thanks

  • kapilbhasin1686

    Old Hand

    Points: 341

    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?

  • Paul Brewer

    SSCrazy

    Points: 2858

    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 36 total)

You must be logged in to reply to this topic. Login to reply