• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

SQL Database differential backup on network drive

Hi All,

I want to create a differential backup script for my SQL database which I want to create on network drive . I have already configured one full backup script that runs in every one hour which creates backup file on network drive, similar way I want to create a differential backup which will run every 15 mins and file should be create on network drive where my existing full backup file is created. I have already created one folder for differential backup. I want differential backup should create a new file each time when backup job runs with time stamp. I believe I have tried to convey it properly.

Below is my full backup script:

SET @Path = '\\\contrack\CFSMAG_' + CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12) + '.BAK'
TO DISK =@Path
NAME='Full Backup of CFSMag';

This is the network path for differential backup
1 Solution
Brian CroweCommented:
First I have to ask why you are doing a full backup every hour?  That's something that should be done once a week typically and differentials daily.  What recovery mode are you in?
angnaneshAuthor Commented:
Hi BriCrowe,

this is my clients requirement, he wants full backup in every one hour and differential backup in 15 mins. I am using full recovery mode.
Gerald ConnollyCommented:
Have you thought about doing a full once a day and using differentials (every 15 mins) to create synthetic fulls every hour?

How much data are we talking about, obviously not a lot if you can do a Full in less than 15 mins.
Tell your client that is not the way to do database backups. If you want a small PTO then you should use the transnational backup instead. Something like this:

1. FULL backup once a week
2. DIFF backup once a day
3. Transaction log backup every N minutes

Also you can do
1. FULL once a day
2. DIFF every 6hr
3. Tran every N minutes.

I also recommend you very highly to use Ola Hallegrens maintenance scripts. It has the backup stored procedure that does all 3 backups, depending on a parameter, and also takes care of a lots of aspects if needed. You should also use the Index Optimisation and Data Integrity check scripts.  It will help you a lot:

David ToddSenior DBACommented:

I like Ola Hallengren's free maintenance script, that does the backups too

If you run the script as it, it will create jobs that do a full backup, a diff backup, and a log backup of user databases. (And a separate job or two for system databases.)

Then all you have to do is schedule these jobs, and edit the backup destination in the job step.


PS Just noticed that Zberteoc recommended Ola's script as well, and I agree with what he said about the frequency of backups.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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