none
Full database backup automatically skip read-only filegorup RRS feed

  • Question

  • Are there any option in BACKUP DATABASE dbname to disk='path'

    that will skip backing up the read-only filegroup ?

    Saturday, March 28, 2020 8:37 AM

Answers

  • Hi sakurai_db,

    Same as TiborK 's suggestion.

    Partial backups only include the read-write parts of the database by default, and read-only files of the database will not be backed up. Because the read-only file part does not change, always backing it up is a waste of time and effort, so partial backup is very useful when you want to not backing up the read-only file group.

    BACKUP DATABASE dbname READ_WRITE_FILEGROUPS TO DISK = 'path'
    GO

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by sakurai_db Friday, April 3, 2020 4:11 AM
    Monday, March 30, 2020 3:52 AM
  • Hi sakurai_db,

    >its it same as full database backup ? will it also backup transaction log ?

    Partial backup can be said to be an intermediate type between database backup and file backup. If there is no read-only file in a database, then there is no difference between a partial backup and a database backup.

    > will it also backup transaction log ?

    database backup is database backup, log backup is log backup.

    The SQL Server database is divided into data files and log files. To enable the database to recover to a consistent point, the backup not only needs to copy the contents of the database data file, but also the contents of the log file. Then according to the different goals of each backup, we can divide backup into data backup and log backup.

    The scope of data backup can be a complete database, a partial database, a set of files or a file group. Therefore, according to the scope of the backed up data files, it is divided into full database backup, file backup and partial backup.

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by sakurai_db Saturday, April 4, 2020 4:42 AM
    Friday, April 3, 2020 6:34 AM
  • It is the same as a full backup, just that you excluded the data from the read-only filegroups. And, as yuxi explained, a full backup contains both data and log records.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by sakurai_db Saturday, April 4, 2020 4:42 AM
    Friday, April 3, 2020 6:45 AM
    Moderator

All replies

  • Are there any option in BACKUP DATABASE dbname to disk='path'

    that will skip backing up the read-only filegroup ?

    I would have to read the through the documentation for the BACKUP to verify. But then again, you could do that yourself? But anyway, I have not heard of any such option.

    You can however, backup individual filegroups to achieve the same thing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, March 28, 2020 10:05 AM
  • if I have 10 filegroups but only 1 filegroup is read-only. they I have to make 9 backup sql to skip that read-only one which I don't want to backup ?
    Saturday, March 28, 2020 10:31 AM
  • ...or you could read the documentation for BACKUP to find that there actually is an option.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, March 28, 2020 10:58 AM
  • ... and find the READ_WRITE_FILEGROUPS option. :-) 

    Documented here: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Sunday, March 29, 2020 11:01 AM
    Moderator
  • Hi sakurai_db,

    Same as TiborK 's suggestion.

    Partial backups only include the read-write parts of the database by default, and read-only files of the database will not be backed up. Because the read-only file part does not change, always backing it up is a waste of time and effort, so partial backup is very useful when you want to not backing up the read-only file group.

    BACKUP DATABASE dbname READ_WRITE_FILEGROUPS TO DISK = 'path'
    GO

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by sakurai_db Friday, April 3, 2020 4:11 AM
    Monday, March 30, 2020 3:52 AM
  • Hi sakurai_db,

    Is the reply helpful?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, March 31, 2020 1:08 AM
  • Hi sakurai_db,

    Is the reply helpful?

    Is your issue solved?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 1, 2020 3:15 AM
  • Hi sakurai_db,

    Is the reply helpful?

    Do you solve the "Full database backup automatically skip read-ony filegroup"  issue?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, April 3, 2020 1:20 AM
  • its it same as full database backup ? will it also backup transaction log ?
    Friday, April 3, 2020 4:12 AM
  • Hi sakurai_db,

    >its it same as full database backup ? will it also backup transaction log ?

    Partial backup can be said to be an intermediate type between database backup and file backup. If there is no read-only file in a database, then there is no difference between a partial backup and a database backup.

    > will it also backup transaction log ?

    database backup is database backup, log backup is log backup.

    The SQL Server database is divided into data files and log files. To enable the database to recover to a consistent point, the backup not only needs to copy the contents of the database data file, but also the contents of the log file. Then according to the different goals of each backup, we can divide backup into data backup and log backup.

    The scope of data backup can be a complete database, a partial database, a set of files or a file group. Therefore, according to the scope of the backed up data files, it is divided into full database backup, file backup and partial backup.

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by sakurai_db Saturday, April 4, 2020 4:42 AM
    Friday, April 3, 2020 6:34 AM
  • It is the same as a full backup, just that you excluded the data from the read-only filegroups. And, as yuxi explained, a full backup contains both data and log records.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by sakurai_db Saturday, April 4, 2020 4:42 AM
    Friday, April 3, 2020 6:45 AM
    Moderator
  • thanks everyone

    Saturday, April 4, 2020 4:43 AM