Home Forums SQL Server 2008 SQL Server 2008 Administration Create Full Backup with Multiple FileGroups RE: Create Full Backup with Multiple FileGroups

  • October 2, 2013 at 10:23 am

    #1655267

    AndrewSQLDBA (10/2/2013)

    Thanks Michael

    Those other options are what SSMS put there. I used the "wizard" and then did the "script to new window".

    I did a test, but the size of the backup file was really small compared to the data file, that is why I was asking.

    If I wanted to backup only one or two of the filegroups, would it be better to use the "File" Option, or the "FileGroup" option? That is where I am confused also.

    Thanks for your assistance with this.

    Andrew SQLDBA

    The backup file could be much smaller than the database files if you have a lot of free spaces in the data or log files. You can use the script on the following link to analyze the database file sizes and spaced used in detail.

    Get Server Database File Information

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

    It could also be smaller if the backup was compressed. The server level default could have been configured to compress backups, even if you did not specify it.

    As Gail mentioned, it is probably best to stay away from file or filegroup backups. I have managed hundreds of servers myself, and have never had a need for it, so unless you have very special backup needs, you should avoid it.