January 14, 2020 at 8:10 pm
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)
January 14, 2020 at 8:34 pm
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
January 14, 2020 at 8:41 pm
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.
January 14, 2020 at 8:49 pm
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.
January 14, 2020 at 11:22 pm
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
January 15, 2020 at 5:31 am
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.
January 15, 2020 at 5:32 am
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.
January 15, 2020 at 11:48 am
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
January 15, 2020 at 3:59 pm
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply