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
johnojohnoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.