.BAK LARGER THAN DATABASE

[niall5098]

Hi All,

i have a situation whereby the database backup is much larger than the actual database itself.

the database is only 20MB while the backup is 1.2GG.

The sql being run to create the backups is as follows:

DECLARE @dbName VARCHAR(33) -- database name
DECLARE @path VARCHAR(99) -- backup path
DECLARE @fileName VARCHAR(99) -- backup file name
DECLARE @fileQuarter CHAR(1) -- variable portion of file name

SET @path = 'D:\path'

-- Returns a Numeral from 1 to 4
-- Each Quarter you get a new backup file name
SELECT @fileQuarter = CONVERT (char(1),(MONTH(GETDATE())+2)/3, 112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
-- Exclude all System Databases, if Needed -
WHERE name NOT IN ('db_names)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @dbName + '_' + @fileQuarter + '.BAK'
-- Use INIT and SKIP to overwrite previous sets
BACKUP DATABASE @dbName TO DISK = @fileName WITH INIT, SKIP
FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor

I though perhaps the backups were being appended but with the 'INIT' parameter that should not be the case.

The version of MSSQL is express, hence the windows batch file to backup the database.

anyone ever see this issue before?

Thanks,

niall

 

[djj55]

What do you get if you run the backup command at through SSMS?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[yelworcm]

Run this on one of the files:

restore headeronly from disk = 'your file goes here'

This will tell you if you have multiple backups in a single file.

 

[SQLBill]

While you are working on resolving this...track the size of the backup...if it is continuing to grow, you most likely are appending the backups.

How did you determine the size of the existing database?

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[niall5098]

HI All,

issue resolved, it was the wrong script i was looking at, the real script had the 'noninit' parameter which was of course appending to the file, thanks for the help.