Restoring database in dev

  • Feivel

    SSCertifiable

    Points: 7499

    One of my projects involved restoring latest backup file from production to dev SQL server on an ongoing basis. I did manage to create a script to do this but since the output of the below query turns out to be a local path in the prod server I need to convert that path so that it can instead be a network share for e.g. the output result is e:\nativebackups\abc_full.bak instead I would like to convert it into \\xyz\e$\nativebackups\abc_full.bak so I can use it in the remaining script. I am thinking about the "REPLACE" function in SQL server but not sure if that would be a right use of it or if there is something I am doing wrong. Also, is my restore statement constructed correctly. Thank you in advance for the help.

    DECLARE @BackupFull varchar(MAX)
    DECLARE @BackupFullEnd DATETIME
    DECLARE @BackupDiff varchar(MAX)
    DECLARE @BackupDiffStart DATETIME
    DECLARE @cmdFull varchar(MAX)
    DECLARE @cmdDiff varchar(MAX)
    DECLARE @cmdRecover varchar(MAX)


    SELECT TOP 1 @BackupFull = bms.physical_device_name,
    @BackupFullEnd = bs.backup_finish_date
    FROM [DEFGH].msdb.dbo.backupset bs
    INNER JOIN [DEFGH].msdb.dbo.backupmediafamily bms
    ON bs.media_set_id = bms.media_set_id
    INNER JOIN [DEFGH].master.dbo.sysdatabases s
    ON bs.database_name = s.name
    WHERE bs.backup_finish_date BETWEEN DATEADD(HOUR, -24, GETDATE()) AND GETDATE()
    AND s.name = 'ABC'
    AND bs.type = 'D'
    AND device_type != 7
    ORDER BY bs.backup_finish_date DESC



    SELECT TOP 1 @BackupDiff = bms.physical_device_name,
    @BackupDiffStart = bs.backup_start_date
    FROM [DEFGH].msdb.dbo.backupset bs
    INNER JOIN [DEFGH].msdb.dbo.backupmediafamily bms
    ON bs.media_set_id = bms.media_set_id
    INNER JOIN [DEFGH].master.dbo.sysdatabases s
    ON bs.database_name = s.name
    WHERE bs.backup_finish_date BETWEEN DATEADD(HOUR, -12, GETDATE()) AND GETDATE()
    AND s.name = 'ABC'
    AND bs.type = 'I'
    AND device_type != 7
    ORDER BY bs.backup_finish_date DESC



    SET @cmdFull =
    'ALTER DATABASE [ABC] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE [ABC]
    FROM DISK = N''' + @BackupFull + '''
    WITH FILE = 1,
    NORECOVERY, NOUNLOAD, REPLACE, STATS = 10'

    SET @cmdDiff =
    'RESTORE DATABASE [ABC]
    FROM DISK = N''' + @BackupDiff + '''
    WITH FILE = 1,
    NOUNLOAD, REPLACE, STATS = 10'

    SET @cmdRecover =
    'RESTORE DATABASE [ABC] WITH RECOVERY'

    --print @cmd
    EXEC (@cmdFull)


    IF @BackupDiffStart > @BackupFullEnd
    EXEC (@cmdDiff)

    ELSE

    EXEC(@cmdRecover)
  • oogibah

    Right there with Babe

    Points: 777

    What I did in this situation is I created a powershell script to grab the latest backup from the prod share and copy it to the location and then have a restore script to grab it after it migrated.. In this scenario in the powershell copy you can rename the file to something static such as ProdToDev.bak and then you can have a static job on the dev box that runs at a certain time every day looking for that file in that folder.

     

    This may not be the most elegant solution but its one of the most simple methods I used.   Another method I used more recently was an adaptation of this script here:

    https://github.com/MikeFal/PowerShell/blob/master/RestoreAutomation/RestoreAutomation.psm1

     

     

  • Feivel

    SSCertifiable

    Points: 7499

    oogibah wrote:

    What I did in this situation is I created a powershell script to grab the latest backup from the prod share and copy it to the location and then have a restore script to grab it after it migrated.. In this scenario in the powershell copy you can rename the file to something static such as ProdToDev.bak and then you can have a static job on the dev box that runs at a certain time every day looking for that file in that folder.

    This may not be the most elegant solution but its one of the most simple methods I used.   Another method I used more recently was an adaptation of this script here:

    https://github.com/MikeFal/PowerShell/blob/master/RestoreAutomation/RestoreAutomation.psm1

    Appreciate you providing me a solution. However, I am looking to have the script that I posted to be modified in order that it works. Nevertheless, I thank you for the same.

  • oogibah

    Right there with Babe

    Points: 777

    Okay, just FYI you may also run into some permissions issues if the service accounts for your dev environment (hopefully aren't) the same as the service accounts for your production environment.  As for the replace I personally don't see why that shouldn't work.

    Also, if the permissions are different from dev and production on the databases themselves, you'll also need to include a method of backing up and restoring those in order for it to be fully automated.

  • Sue_H

    SSC Guru

    Points: 90371

    Another FYI on the permissions....using an admin share is going to result in some security issues as well. Using servername\e$ is the admin share.

    Sue

  • Feivel

    SSCertifiable

    Points: 7499

    oogibah wrote:

    Okay, just FYI you may also run into some permissions issues if the service accounts for your dev environment (hopefully aren't) the same as the service accounts for your production environment.  As for the replace I personally don't see why that shouldn't work.

    Also, if the permissions are different from dev and production on the databases themselves, you'll also need to include a method of backing up and restoring those in order for it to be fully automated.

    I will take care of those...not a major concern as long as the script does what it needs to the rest shall fall in place.

  • Feivel

    SSCertifiable

    Points: 7499

    Sue_H wrote:

    Another FYI on the permissions....using an admin share is going to result in some security issues as well. Using servername\e$ is the admin share.

    Sue

    Thanks! Sue but no problem with that. I am only concerned of the result and needs to fix that...let me know if you are able to do some magic on the script.

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    The RESTORE statement looks OK, although I think I'd put an explicit NORECOVERY in the differential restore.  My opinion doesn't count, though - it's whether it works when you run it that's important.

    As for converting to UNC, you'll want to use SERVERPROPERTY('ComputerName'), although that's from memory, so check my syntax against the documentation.  You can use REPLACE or STUFF to do the character manipulation.

    John

     

  • oogibah

    Right there with Babe

    Points: 777

    John Mitchell-245523 wrote:

    The RESTORE statement looks OK, although I think I'd put an explicit NORECOVERY in the differential restore.  My opinion doesn't count, though - it's whether it works when you run it that's important.

    As for converting to UNC, you'll want to use SERVERPROPERTY('ComputerName'), although that's from memory, so check my syntax against the documentation.  You can use REPLACE or STUFF to do the character manipulation.

    John

     

    I do recall in some cases, its necessary to do

    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

Viewing 9 posts - 1 through 9 (of 9 total)

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