Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Database differential backup on network drive

Posted on 2014-02-21
5
Medium Priority
?
186 Views
Last Modified: 2014-10-27
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:

USE CFSMag;
GO
DECLARE @Path NVARCHAR(1000)
SET @Path = '\\10.91.12.191\contrack\CFSMAG_' + CONVERT(CHAR(6), CURRENT_TIMESTAMP, 12) + '.BAK'
BACKUP DATABASE CFSMag
TO DISK =@Path
WITH FORMAT,
MEDIANAME='D_SQLServerBackups',
NAME='Full Backup of CFSMag';
GO

This is the network path for differential backup
\\10.91.12.191\contrack\Incremental_Backup\
0
Comment
Question by:angnanesh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39876124
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?
0
 

Author Comment

by:angnanesh
ID: 39876149
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.
0
 
LVL 17

Expert Comment

by:Gerald Connolly
ID: 39876220
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.
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1000 total points
ID: 39876514
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:

http://ola.hallengren.com/
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39878401
Hi,

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.

HTH
  David

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question