Post reply

Backup using a device vs. a file location

  • October 19, 2005 at 1:24 pm

    #111103

    I'm taking a full backup using a device and the file size is much less than taking backup without it.  Don't understand why!  The size diff. is around 6 MB vs. 500 MB.

    BACKUP DATABASE [dbname] TO  DISK = N'D:\MSSQL7\BACKUP\dbname' WITH  NOINIT ,  NOUNLOAD , 

    NAME = N'dbname backup', 

    NOSKIP ,  STATS = 10,  NOFORMAT

    BACKUP DATABASE [dbname] TO [device] WITH  NOINIT ,  NOUNLOAD , 

    NAME = N'dbname Bak', 

    NOSKIP ,  STATS = 10,  NOFORMAT

    I appriciate any help.

  • October 21, 2005 at 12:48 pm

    #599259

    Could it be you already have previous backups in the file, but you only just created the device? I see you are using NOINIT which just adds the new backup to the end of the file.

  • October 21, 2005 at 12:59 pm

    #599268

    Thanks Glynne.

    I think both of those will append even though Portable DBA SQL Server book says that if you don't create a device, you can not append.  That is not correct I belive.

  • October 21, 2005 at 2:05 pm

    #599300

    How big is the database?

    My test showed no difference in the size.

    Is the device and the file on the same drive?

  • October 21, 2005 at 2:12 pm

    #599304

    It's only 36 Mb.  I ran the backup on two different drives and they're the same size as well.  I'm trying to compare my backups with the previous dba and the size different is concerning.  I think the book is incorrect.  Both backups are appending.  Another words, you can append data without creating a device.

  • October 21, 2005 at 2:19 pm

    #599308

    You definitly can append without using a device. I do it all the time.

  • October 21, 2005 at 2:20 pm

    #599313

    Thanks again.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply