Need Tutorial on Differential Backup

Question

Post reply

Need Tutorial on Differential Backup

Seggerman-675349

Hall of Fame

Points: 3563
More actions
February 26, 2018 at 9:28 am

#410269

I need to backup a SQL Server 2016 database weekly - and take incremental backups daily
I found this snippet:
-- Create a full database backup first.
BACKUP DATABASE MyAdvWorks
TO MyAdvWorks_1
WITH INIT
GO
-- Time elapses.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE MyAdvWorks
TO MyAdvWorks_1
WITH DIFFERENTIAL
GO
well, the database device still points to the same backup file, right? so shouldn't there be a step to remove MyAdvWorks_1 and point it to a different backup file each time? everything is on disk - no tapes involved
could someone help me with the missing steps? much obliged
also the production restore
a newbie DBA, not by choice!
February 28, 2018 at 4:12 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 1

Hello there.

I recommend using Ola Hallengren script tools for database backup.
https://ola.hallengren.com/sql-server-backup.html

There's all the documentation you need.
Including full, differentials and transaction log, you can tweak it to fit your needs too.

To get where you want to go, you'll need to tweak these scripts and make jobs exactly whenever you want them to execute.

Of course you can build your own scripts too and even learn from Ola's scripts.

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

please follow this article
https://www.red-gate.com/simple-talk/sql/backup-and-recovery/sql-server-2014-backup-basics/

Alex S

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I strongly recommend NOT appending backups to the same file. Makes it really easy to lose all your backups instead of one.
Each one to its own file, preferably with the datetime as part of the file name

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 4

thanks folks but it doesn't quite answer my question
do I create a full backup to a device - and set the device through T-SQL so the file name has a date as part of it
drop the device and recreate with a new name the indicates it is a diff
run a differential backup
is this workable?
if I create backups to disk will it automatically create a differential backup ?

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

No. Backup to a file. Ignore devices.

BACKUP DATABASE DBName
TO DISK = <file name here>

The file name can be a variable, so you can build up the string with the date.

If you create backups to disk, it'll create exactly the type of backup you specify. Want a differential, specify WITH DIFFERENTIAL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 6

Seggerman-675349 - Monday, February 26, 2018 1:04 PM
thanks folks but it doesn't quite answer my question
do I create a full backup to a device - and set the device through T-SQL so the file name has a date as part of it
drop the device and recreate with a new name the indicates it is a diff
run a differential backup
is this workable?
if I create backups to disk will it automatically create a differential backup ?
thanks

This is a script i made using Adventure Works when i was learning.:
First part is FULL Backup
Second one is Differential backup - you can see WITH DIFFERENTIAL
Third one is Log Backup it explicitly states that it is one.

--This is the full backup part --Full backup, Keeps the changes of the database to a point in time backup database [AdventureWorks2012] TO DISK = N'E:\BACKUPS\AVWFULL181217.bak'--direccion donde se almacenara el full WITH NOFORMAT, NOINIT, NAME =N'ADVENTUREWORKS2012FULLDBBACKUP', DESCRIPTION='BACKUP AL DIA 18-12-2017', SKIP, NOREWIND, NOUNLOAD,STATS=10 GO --Differential backup, Keep the changes made to the database till the last full backup BACKUP DATABASE [AdventureWorks2012] TO DISK = N'E:\BACKUPS\AVWDIFF181217.bak' --direccion donde se almacenara el differential WITH DIFFERENTIAL, --diciendo explicitamente que tipo de backup es, aqui podemos ver que es un diferencial NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

--Transaction log backup, Keeps the changes made to the database until the last differential BACKUP LOG [AdventureWorks2012] TO DISK = N'E:\BACKUPS\AVWLOG181217.bak'--direccion donde se almacenara el transaccional WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Sorry for the spanish comments its my native language.

As Gail said before you can also declare variables as backupdate as DATE and pass getdate() as result and build a script that creates name for your backup.
Theres an example in the website below.
https://solutioncenter.apexsql.com/create-daily-database-backups-with-unique-names-in-sql-server/

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 7

Seggerman-675349

Hall of Fame

Points: 3563

February 26, 2018 at 1:58 pm

#1981187

yes, that is what I needed - thanks folks

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

Seggerman-675349 - Monday, February 26, 2018 1:58 PM
yes, that is what I needed - thanks folks

Not quite. It's a good example of the 3 types of backups but you need to write it as dynamic SQL so that you can change the file names over time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden - Monday, February 26, 2018 4:07 PM
Seggerman-675349 - Monday, February 26, 2018 1:58 PM
yes, that is what I needed - thanks folks
Not quite. It's a good example of the 3 types of backups but you need to write it as dynamic SQL so that you can change the file names over time.

No you don't.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 10

SET @Filename ='C:\bak\PowerHouse_' + CONVERT(VARCHAR(8),GetDate(),112) + '.bak'

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

GilaMonster - Tuesday, February 27, 2018 1:02 AM
Jeff Moden - Monday, February 26, 2018 4:07 PM
Seggerman-675349 - Monday, February 26, 2018 1:58 PM
yes, that is what I needed - thanks folks
Not quite. It's a good example of the 3 types of backups but you need to write it as dynamic SQL so that you can change the file names over time.
No you don't.

Oh my... bad habits are hard to break. I'm not sure why I've been doing it with dynamic SQL all these years and never thought to try otherwise. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

my article below details differential backups and log shipping, but it should give you an insight into what diffs are

http://www.sqlservercentral.com/articles/differential/93482/

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉