Backing up a database using T-SQL

Question

Hello,

There is a database at work which was backed up using two different methods. The two backup files are very different in size (one is 12MB and one is 91 MB). I am told that the T-SQL code that was used to create the 12MB file is as follows: 

BACKUP DATABASE [database1] TO DISK = @FileName WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

The other backup file was created in a program called myLittleAdmin.

I did not do the backup but, I am being asked why the files are so different. I know this may be a very difficult question to try and answer remotely but, I would appreciate any help trying to answer this.

Thanks!

Answer 1

Hello,

One backup file may contain multiple backup sets. A full backup is usually greater in size than differential and transaction log backups.

Try to use the following article to know what the SQL Server backup file contains.

http://www.mssqltips.com/sqlservertip/1150/what-is-in-your-sql-server-backup-files/

You may find help on MyLittleAdmin forum about how to know what is contained on the MyLittleAdmin backup file.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Hi,

I think you are appending to the backup device in the myLittleAdmin program with NOINIT option. So the file size is looking 91 MB. The backups will be in multiple in appended copies which makes the size to 91 MB. You can use the below command on the backup file of myLittleAdmin program and check,

Restore headeronly from disk ='location\backupfile.bak'

---

Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.