MS SQL daily diff backup schedule

Dear all,

we have a backup schedule like this:

hourly differential backup
As we have weekly differential everyday backup except sunday, is that job also means, for each of the day, it execute the differential backup every one hour ?

as the full backup only start on sunday, anyway to make sure that once the sunday full backup was done, the daily every one hour differential backup will start right after that ?

I think I need one more schedule SQL job for that.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
As we have weekly differential everyday backup except sunday, is that job also means, for each of the day, it execute the differential backup every one hour ?
=> YES, starting between 9pm and last one starting 11pm

as the full backup only start on sunday, anyway to make sure that once the sunday full backup was done, the daily every one hour differential backup will start right after that ?
=> in sql server jobs, you cannot make "relations" between jobs like this easily (run 1 job only if the other one is not running)

to "solve" this, you may add a first step in the differential backup (and run it actually every day), that will check if the full backup job is running, and return an error if it does.
the job step would then be configured to continue to next step (the actual backup step) if ok, and fail otherwise

the step would be of type sql, and look eventually like this:
DECLARE @cnt TINYINT;
SELECT @cnt= count(*) SELECT
    job.Name, job.job_ID
    ,job.Originating_Server
    ,activity.run_requested_Date
    ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
    msdb.dbo.sysjobs_view job 
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
WHERE
    run_Requested_date is not null 
    AND stop_execution_date is null
    AND job.name like 'Your full backup jo name%'

IF @dbState >0
BEGIN
  RAISERROR('Full Backup is Running', 11, 1);
END

Open in new window


hope this helps
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
May I ask why do you need a differential backup every hour?
If the database is on full recovery model you can perform an hourly transaction log backup and leave a single daily differential backup to run during the evening/night.
0
marrowyungSenior Technical architecture (Data)Author Commented:
dear all,

sorry for the delay reply as I have been on vocation between UK and Sydney and then back to my home town for 2 weeks.

Vitor Montalvão,

"May I ask why do you need a differential backup every hour?"

management request that if server failed and we at most lost 1 hour of data.

"If the database is on full recovery model you can perform an hourly transaction log backup and leave a single daily differential backup to run during the evening/night. "

then it keep 3 sets of backup files, right?

then why just do sunday full backup and daily differential backup? then 2 sets of files only ! right? also in full recovery mode, this kind of log backup will be huge, right?

so what you mean is, one monthly full backup, daily differential backup and hours transaction log backup, but it takes extra steps to recover as one more script required and more space for storing the backup, right?

Guy Hengel [angelIII / a3],

"=> in sql server jobs, you cannot make "relations" between jobs like this easily (run 1 job only if the other one is not running)
"
that's why I don't think I can fix it in job level.

"to "solve" this, you may add a first step in the differential backup (and run it actually every day), that will check if the full backup job is running, and return an error if it does."

first step means this checking step will be the first to execute hourly and see if sunday full backup was done, then it is done then start hourly differential backup on sunday ?

differential backup run everyday means it runs once per day ? this is not what I want, right?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
management request that if server failed and we at most lost 1 hour of data.
Can be performed with transaction log backups. Will be faster and smaller backups and you'll be able to restore to an exact point in time (till the exact millisecond if you want to).

then it keep 3 sets of backup files, right?
Yes. Full, Diff and TLog backups.

then why just do sunday full backup and daily differential backup?
Because then you won't be able to recover until the last hour but last day only.

also in full recovery mode, this kind of log backup will be huge, right?
No. You'll have more files to restore but each one should be small unless your database is highly transactional (thousands of changes by second).

so what you mean is, one monthly full backup, daily differential backup and hours transaction log backup, but it takes extra steps to recover as one more script required and more space for storing the backup, right?
No monthly full backups but weekly. Yes, restore will take extra files (TLog backup) but the trade off is a more secure way to keep a copy of your data. And how many times you think you'll need to perform a restore? Never should be what you expect, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

"Because then you won't be able to recover until the last hour but last day only"

no full backup and differential backup let you restore everything up to what the last diff backup give, if I do diff backup every hour and we will not lost data for more than 1 hour.

so not last day, right?

"No. You'll have more files to restore but each one should be small unless your database is highly transactional (thousands of changes by second)."

we have some e-commerce site here use log shipping and that one backup log and in full recoevery model, this kind of site can give log size around 100-200MB and even more. this is what concern me.

"And how many times you think you'll need to perform a restore? Never should be what you expect, right?"

yes but we will do restore test .........
0
marrowyungSenior Technical architecture (Data)Author Commented:
btw, we use litespeed to do backup and do you know the transaction log script? I am checking too.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
if I do diff backup every hour and we will not lost data for more than 1 hour.
Right but you won't be able to restore to any point on time. And each diff backup will be bigger than the last one. So it will take more time.

we have some e-commerce site here use log shipping and that one backup log and in full recoevery model, this kind of site can give log size around 100-200MB and even more.
That can happen when a database has too many transactions. It's also an hourly transaction log backup?

yes but we will do restore test .........
So? Tests are always good to be done and they aren't time pressure, right? You can even take a full day if you want. The idea is to verify that everything runs fine (no corrupted or missing files, correct paths and permissions, ...).
0
marrowyungSenior Technical architecture (Data)Author Commented:
Guy Hengel [angelIII / a3],

tks for this code:

SELECT
    job.Name, job.job_ID
    ,job.Originating_Server
    ,activity.run_requested_Date
    ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
    msdb.dbo.sysjobs_view job 
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
WHERE
    run_Requested_date is not null 
    AND stop_execution_date is null
    AND job.name like 'Repository Daily iXAlert_Mart Diff Backup'

Open in new window


This logic works fine but if IF @dbState >0 it raise an error to where? who will receive it,  anything like IF @dbState =0 then go to next step ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
The SQL agent job will receive the error, and on each step, you can configure what to happen "next", based on if an error was encountered or not
0
marrowyungSenior Technical architecture (Data)Author Commented:
you mean on failure and on success ?

so

IF @dbState >0
BEGIN
  RAISERROR('Full Backup is Running', 11, 1);
END

Open in new window


already represent it is a on failure condition for this step ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

I am sorry to reply this ticket late:

"Yes, restore will take extra files (TLog backup) but the trade off is a more secure way to keep a copy of your data"

why Tlog backup is a more secure way  to keep a copy of your data, the file can take away by someone, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The secure there is about data loss and not really the security of the file. Sorry by not being so clear.
0
marrowyungSenior Technical architecture (Data)Author Commented:
so one last thing I before I close this post is, if I do full, diff and Tlog backup.

1) there will be 1 full backup for sure.
2) there will be only 1x diff backup .
3) will there be a lot of Tlog backups ?

and when restoring, when I do restore by all of them, when restoring 1) and 2), the DB have to be in non recoverable state before TLOG can be backup ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
3) will there be a lot of Tlog backups ?
Normally, yes. But depends on how often are you running the tlog backups. Usually with very transactional and critical databases we run a tlog backup every 15 minutes. Less transactional (i.e. less changes) you run at maximum once by hour, sometimes every two hours.


and when restoring, when I do restore by all of them
Restore always the last full backup and the last diff backup, then all tlog backups until the recovery point that you wish. All restores need to run with NORECOVERY option. Only the last tlog restore needs to run with RECOVERY option.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Normally, yes. But depends on how often are you running the tlog backups. Usually with very transactional and critical databases we run a tlog backup every 15 minutes. Less transactional (i.e. less changes) you run at maximum once by hour, sometimes every two hours."

ok, each TLog backup create a separate TLog files, each TLog backup will have diff name ?

" then all tlog backups until the recovery point that you wish. "

if they all have diff file name, how can we restore all of them at once, in a single script preferably ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
ok, each TLog backup create a separate TLog files, each TLog backup will have diff name ?
Also depends if you are giving different name for the tlog backup file. If you're using the same name then all backups will be in that single file (INIT option).


if they all have diff file name, how can we restore all of them at once, in a single script preferably ?
You'll need to add all necessary restore statements. Example for restoring with 3 tlog backups:
-- Specify last full backup file
RESTORE DATABASE db_name
FROM DISK='C:\Backups\db_name_full.bak
WITH NORECOVERY
GO

-- Specify last diff backup file
RESTORE DATABASE db_name
FROM DISK='C:\Backups\db_name_diff.bak
WITH NORECOVERY
GO

-- Specify first tlog backup file after last diff
RESTORE DATABASE db_name
FROM DISK='C:\Backups\db_name_tlog1.trn
WITH NORECOVERY
GO

-- Specify second tlog backup file after last diff
RESTORE DATABASE db_name
FROM DISK='C:\Backups\db_name_tlog2.trn
WITH NORECOVERY
GO

-- Specify last tlog backup file
RESTORE DATABASE db_name
FROM DISK='C:\Backups\db_name_tlogN.bak
WITH RECOVERY
GO

Open in new window

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
marrowyungSenior Technical architecture (Data)Author Commented:
"Also depends if you are giving different name for the tlog backup file. If you're using the same name then all backups will be in that single file (INIT option)."

basically we are going to use the Ola solution for that  and the command we use is:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'J:\Backups\Ola\Diff',
@BackupType = 'DIFF',
@Compress = 'Y',
@CleanupTime = '192'  

Open in new window


so there will be no way to give a name to each TLog files, right?

by your statement, you mean we can specify a new name for each newly created TLog?

by your restore script, is that mean it has to be hard code?

we are trying to convert all maintence plan backup job to Ola solution, but can we have one SQL job for restore these diff, full, and TLog backup by Ola ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
so there will be no way to give a name to each TLog files, right?
With Ola's solution, no.


by your statement, you mean we can specify a new name for each newly created TLog?
Yes you can, and is what Ola's script do. It uses the current date time for the backup file name so there will be always different files.


by your restore script, is that mean it has to be hard code?
we are trying to convert all maintence plan backup job to Ola solution, but can we have one SQL job for restore these diff, full, and TLog backup by Ola ?
Hardcoded, yes. I know that Ola was working in the Restore part for his maintenance solution. You can send him an email to see when that version will be released.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"With Ola's solution, no."

so creating a sQL job only for TLOG backup can, right? same name all the time you mean by hard code?

"Hardcoded, yes. I know that Ola was working in the Restore part for his maintenance solution."
yeah, no restore at all and all restore is native SQL restore, which mean Ola just make the backup solution easier by a single script only ! but it is not that good !

at least we can't hard code the backup file name.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"With Ola's solution, no.
"

OM
0
marrowyungSenior Technical architecture (Data)Author Commented:
but can still copy and paste the file name created by Ola, agree ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure you can but will be always a manual task.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, got it.
0
marrowyungSenior Technical architecture (Data)Author Commented:
sorry for hold it for a long time, it is time for me to start Ola solution.
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

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.