[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Restoring a full and diff where the full backup file name changes every 28 day

Hi,

Find below an SQL procedure which will restore a full backup and a differential. I've managed to work out the differential file name that changes every day based on the date.  However the full backup also changes every 28 days and only requires a full restore with recovery mode only. How can I make the full backup automated as well? :)

USE [Database1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[sp_CC_FullRestore_Diff]
AS
BEGIN
ALTER DATABASE RiO_Sirona_Live SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE TEST_Database

DECLARE @datestring varchar(112)
DECLARE @diffpath varchar(80)
--- These lines below allow the script to identify the latest differential backup file from looking at the date from the server
SET @datestring = replace(convert(varchar(10), getdate(), 103), '/', '') ;
SET @diffpath = 'C:\DB_'+@datestring+'.bak'
--- These lines below finds the  full backup file and  restores with the norecovery option so a differential can be added later
RESTORE DATABASE RiO_Sirona_Live FROM DISK='E:\sftp\inbound\Test_Database_28072014.bak'  --- ( this changes every month example 28082014.bak but the rest of the file name stays the same)
--- (only required when differentials are needed)
WITH NORECOVERY,
MOVE 'RiO_RVJC_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.mdf',
MOVE 'Rio_RVJC_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ldf',
MOVE 'RiO_RVJC_FullText' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ndf'
--- This line below finds the differential backup from the value diffpath
RESTORE DATABASE RiO_Sirona_Live FROM DISK = @diffpath
WITH RECOVERY,
MOVE 'RiO_RVJC_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.mdf',
MOVE 'Rio_RVJC_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ldf',
MOVE 'RiO_RVJC_FullText' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ndf'
END
0
johnojohno
Asked:
johnojohno
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
DECLARE @datestring varchar(112)
 DECLARE @backuppath varchar(500)
 DECLARE @diffpath varchar(80)
 --- These lines below allow the script to identify the latest differential backup file from looking at the date from the server
 SET @datestring = replace(convert(varchar(10), getdate(), 103), '/', '') ;
 SET @backuppath = 'E:\sftp\inbound\Test_Database_28' + SUBSTRING(@datestring, 3, 8) + '.bak'
 SET @diffpath = 'C:\DB_'+@datestring+'.bak'
 
 --- These lines below finds the  full backup file and
 --- restores with norecovery so a differential/tran log(s) could be applied also
 RESTORE DATABASE RiO_Sirona_Live FROM DISK=@backuppath
 WITH NORECOVERY,
 MOVE 'RiO_RVJC_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.mdf',
 MOVE 'Rio_RVJC_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ldf',
 MOVE 'RiO_RVJC_FullText' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ndf'
 --- This line below finds the differential backup from the value diffpath
 RESTORE DATABASE RiO_Sirona_Live FROM DISK = @diffpath
 WITH NORECOVERY
 --if needed, apply tran log(s) here
 --recover the db
 RESTORE DATABASE RiO_Sirona_Live WITH RECOVERY
0
 
johnojohnoAuthor Commented:
Hi ScottPletcher,  I've had a look the script and when you are in different month from the last full backup it looks at the current month you are in, which will not be available util the 28th day.
For example, Full Backup Name on FULL_28-08-2014 and today's date was 01-09-2014 your script would be looking for FULL_28-09-2014 on that day, which not currently available. The full backup in month 9 would be available on the 28th day of the month. The script needs to refer the previous month until the 28th day. Could we some how create a temporary table with the correct date value and after the 28 day change the month value?  

if day is equal to or greater than 28th day of the month, then change value to the next month?
0
 
Scott PletcherSenior DBACommented:
Yes, sorry, I was trying to piggy-back onto the date you'd already calculated, but I needed to add a new date variable to allow for the day check.


  DECLARE @datestring varchar(112)
  DECLARE @backupdatestring varchar(30)
  DECLARE @backuppath varchar(500)
  DECLARE @diffpath varchar(80)
  --- These lines below allow the script to identify the latest differential backup file from looking at the date from the server
  SET @datestring = replace(convert(varchar(10), getdate(), 103), '/', '') ;
  SET @backupdatestring = replace(convert(varchar(10),  dateadd(month, case when day(getdate()) >= 28 then 0 else -1 end, getdate()), 103), '/', '') ;
  SET @backuppath = 'E:\sftp\inbound\Test_Database_' + @backupdatestring + '.bak'
  SET @diffpath = 'C:\DB_'+@datestring+'.bak'

--- These lines below finds the  full backup file and
  --- restores with norecovery so a differential/tran log(s) could be applied also
  RESTORE DATABASE RiO_Sirona_Live FROM DISK=@backuppath
  WITH NORECOVERY,
  MOVE 'RiO_RVJC_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.mdf',
  MOVE 'Rio_RVJC_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ldf',
  MOVE 'RiO_RVJC_FullText' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB.ndf'
  --- This line below finds the differential backup from the value diffpath
  RESTORE DATABASE RiO_Sirona_Live FROM DISK = @diffpath
  WITH NORECOVERY
  --if needed, apply tran log(s) here
  --recover the db
  RESTORE DATABASE RiO_Sirona_Live WITH RECOVERY
0
 
johnojohnoAuthor Commented:
Many thanks Scott its now working perfectly and I've also readded the following to the set @backuppath line:

SET @backuppath = 'E:\sftp\inbound\Test_Database_28' + SUBSTRING(@datestring, 3, 8) + '.bak'

As the 28th day is always the same value on the full backup.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now