doubt about SQL backup

[rookie11]

Hi guys
i hav doubt about SQL backup , mainly differential backup, it is SQL server 2008

recent full SQL backup ran successfully.
option 1 if select below mentioned script what will happen
OPERATION BACKUP
DATABASE $ALL
SQLHOST ""
SQLINSTANCE ""
NBSERVER ""
Exclude "master"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
BROWSECLIENT ""
DUMPOPTION INCREMENTAL
NUMBUFS 2
ENDOPER TRUE

option 2 if select below mentioned script what will happen
OPERATION BACKUP
DATABASE $ALL
SQLHOST ""
SQLINSTANCE ""
NBSERVER ""
MAXTRANSFERSIZE 6
BLOCKSIZE 7
BROWSECLIENT ""
DUMPOPTION INCREMENTAL
NUMBUFS 2
ENDOPER TRUE

[Yogesh9881]

1. Master databse will INCLUDE in 2nd script  ...

2. Master database will Exclude in 1st script  ...

[Marianne]

Is your 'doubt' about DUMPOPTION INCREMENTAL or about Exclude "master" ?

**** EDIT ****

If I read the NBU for MSSQL manual correctly, DUMPOPTION INCREMENTAL is only used in restore script.

See p. 196 of NBU for Microsoft SQL Server Admin Guide  http://www.symantec.com/docs/DOC3670 :

Sample 10 – Stage a database restore from a database backup, a differential backup, and a series of transaction backups This example shows a script that you generate in the Restore Microsoft SQL Server Objects dialog box.

OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "DatabaseA"
# The following image is type: Full
NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.db.DatabaseA.~.7.001of001.20060701094227..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE

OPERATION RESTORE
OBJECTTYPE DATABASE
DUMPOPTION INCREMENTAL
DATABASE "DatabaseA"
# The following image is type: Full database differential
NBIMAGE "cadoo.MSSQL7.CADOO\SECOND.inc.DatabaseA.~.7.001of001.20060701103323..C"
SQLHOST "CADOO"
SQLINSTANCE "SECOND"
NBSERVER "BOW"
BROWSECLIENT "CADOO"
MAXTRANSFERSIZE 0
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
ENDOPER TRUE

OPERATION RESTORE
OBJECTTYPE TRXLOG
DATABASE "DatabaseA"
# The following image is type: transaction log
NBIMAGE "cadoo.MSSQL7.CADOO\

Keywords on p.109:

DUMPOPTION  INCREMENTAL:
Specifies INCREMENTAL restoring from an incremental backup.

[Mark_Solutions]

I did think that the "Exclude" should be all upper case?

SQL Transaction Logs backups should look like the first one as at least the Master database should be excluded from log backups

[rookie11]

i will elaborate a bit more.

we hav to run  SQL full backup on saturdays n SQL incremental backups on mondy to friday
as i mentioned earlier Full backup completed already. I hav 2 configure incremental scripts, i checked out scripts from other 2 SQL servers
and found out scripts which i hav pasted in my 1st post.
but not sure which should i use to configure incremental SQL backup

@berg DUMPOPTION INCREMENTAL comes in script when we configure through GUI.so my doubt about Exclude "master" should use it or not.

@ yogesh point u mentioned i understood it.  question is  what script i should use 1st or 2nd  ???

[Mark_Solutions · Accepted solution]

OK - An incremental in SQL terms would be a transaction log backup - your scripts are not, they are database backups with one excluding the Master Database.

For SQL the default application backup schedule does the actual work - your schedule will not be based on being full or incremental - you just need different scripts - and they will have to go in dofferent policies.

You should really go onto the client to create the script via the SQL Client GUI - it should be a transaction log backup and it should look like:

OPERATION BACKUP
DATABASE $ALL
SQLHOST ""
SQLINSTANCE ""
NBSERVER ""
EXCLUDE "master"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
BROWSECLIENT ""
OBJECTTYPE TRXLOG
NUMBUFS 2
ENDOPER TRUE

Also bear in mind that the retention of SQL backups comes from the default application schedule

So you may need daily, weekly, monthly and logs policies

Hope this helps