SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Tutorial on Differential Backup


Need Tutorial on Differential Backup

Author
Message
Seggerman-675349
Seggerman-675349
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2845 Visits: 608
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!
Alejandro Santana
Alejandro Santana
SSC Eights!
SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)

Group: General Forum Members
Points: 989 Visits: 1120
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
AlexSQLForums
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12752 Visits: 2931
please follow this article
https://www.red-gate.com/simple-talk/sql/backup-and-recovery/sql-server-2014-backup-basics/

Alex S
GilaMonster
GilaMonster
SSC Guru
SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)

Group: General Forum Members
Points: 910442 Visits: 48846
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
Seggerman-675349
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2845 Visits: 608
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)

Group: General Forum Members
Points: 910442 Visits: 48846
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
Alejandro Santana
SSC Eights!
SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)SSC Eights! (989 reputation)

Group: General Forum Members
Points: 989 Visits: 1120
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
Seggerman-675349
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2845 Visits: 608
yes, that is what I needed - thanks folks
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)SSC Guru (883K reputation)

Group: General Forum Members
Points: 883664 Visits: 47914
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)

Group: General Forum Members
Points: 910442 Visits: 48846
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search