Backup file .bak size not increase

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Backup file .bak size not increase

Author

Topic

soushi01
Starting Member

8 Posts

Posted - 2014-11-17 : 22:09:05

Hi, Im Newbie SQL server user

Need you all advise I have set Daily Backup database by using Maintenance Wizard features. But however i didn't see .bak file size increase. I runFullBackup.
So i have to run backup by Manually then only can see the .bak file size has increase.

any suggestion ?

thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-11-18 : 01:47:34

Some things to check: 1) Are you actually running a FULL BACKUP with the maintenance wizard ? You can check the wizard logs, the backup history table in msdb ?
http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

soushi01
Starting Member

8 Posts

Posted - 2014-11-18 : 02:20:04

quote:
Originally posted by jackv

Some things to check: 1) Are you actually running a FULL BACKUP with the maintenance wizard ? You can check the wizard logs, the backup history table in msdb ?
http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Hi Jack

Yes, I actually run FULL BACKUP with the maintenance wizard.
And i already check backup history table that you provide, file size is same

thanks

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-11-18 : 03:41:38

Can you open the plan, click on 'View T-SQL' button and verify the queries.

Hema Sunder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 12:43:32

I'm not following your question. Is the file 0 bytes? Are you overwriting the file for each new backup or appending backups to the same file? Please explain in more detail for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-11-19 : 01:50:56

If possible, could you capture via a trace the actual BACKUP DATABASE statement which executes.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

soushi01
Starting Member

8 Posts

Posted - 2014-11-19 : 11:33:15

quote:
Originally posted by tkizer

I'm not following your question. Is the file 0 bytes? Are you overwriting the file for each new backup or appending backups to the same file? Please explain in more detail for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi,Kizer

The current file size (.bak) is not 0 bytes.

For example currently file size (.bak) is 362,450 kb ,
i set run backup by daily 10am, assume that if everyday got transaction, file size (.bak) should be increase, but i checked .bak file size is still the same everyday.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 11:49:27

But how much data are you adding to the database each day? Are you rebuilding indexes during a maintenance window? How about the fill factor on the indexes?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

soushi01
Starting Member

8 Posts

Posted - 2014-11-19 : 11:57:37

quote:
Originally posted by jackv

If possible, could you capture via a trace the actual BACKUP DATABASE statement which executes.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Hi Jack,
Is it this one ? Please correct me is wrong

thanks

soushi01
Starting Member

8 Posts

Posted - 2014-11-19 : 12:05:32

quote:
Originally posted by sunder.bugatha

Can you open the plan, click on 'View T-SQL' button and verify the queries.

Hema Sunder

Hi, Hema

This is View T-SQL script

BACKUP DATABASE [adventureworks2012] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\adventureworks2012_backup_2014_11_20_010224_6482226.bak' WITH NOFORMAT, NOINIT, NAME = N'adventureworks2012_backup_2014_11_20_010224_6482226', SKIP, REWIND, NOUNLOAD, STATS = 10

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 12:34:04

Soushi01, it's because you are using NOINIT in your command. NOINIT means to APPEND backups into the same file. The other way you are doing the backups is likely using INIT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

soushi01
Starting Member

8 Posts

Posted - 2014-11-20 : 04:20:25

quote:
Originally posted by tkizer

Soushi01, it's because you are using NOINIT in your command. NOINIT means to APPEND backups into the same file. The other way you are doing the backups is likely using INIT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi,

maybe I Where to edit script like NOINIT change to INIT,
i only can view T-SQL script,cannot edit

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-20 : 11:33:47

quote:
Originally posted by soushi01

quote:
Originally posted by tkizer

Soushi01, it's because you are using NOINIT in your command. NOINIT means to APPEND backups into the same file. The other way you are doing the backups is likely using INIT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi,

maybe I Where to edit script like NOINIT change to INIT,
i only can view T-SQL script,cannot edit

thanks

I don't know what you mean. Why can't you edit a script?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

soushi01
Starting Member

8 Posts

Posted - 2014-11-20 : 21:38:53

quote:
Originally posted by tkizer

quote:
Originally posted by soushi01

quote:
Originally posted by tkizer

Soushi01, it's because you are using NOINIT in your command. NOINIT means to APPEND backups into the same file. The other way you are doing the backups is likely using INIT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi,

maybe I Where to edit script like NOINIT change to INIT,
i only can view T-SQL script,cannot edit

thanks

I don't know what you mean. Why can't you edit a script?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi,Kizer
I thought here can direct edit T-SQL script, but cant ...

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-21 : 12:52:26

Just run your backup command in a query window in Management Studio.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources