INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

.BAK LARGER THAN DATABASE

.BAK LARGER THAN DATABASE

(OP)
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

RE: .BAK LARGER THAN DATABASE

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!

RE: .BAK LARGER THAN DATABASE

Run this on one of the files:

CODE

restore headeronly from disk = 'your file goes here' 
This will tell you if you have multiple backups in a single file.

RE: .BAK LARGER THAN DATABASE

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: What should I know before I post?

RE: .BAK LARGER THAN DATABASE

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close