Backup of of mssqlsystemresource

Robert Jubb 46 Reputation points
2021-03-02T09:28:25.57+00:00

Hi there,

When we apply a CU or service pack to our instances of SQL Server, (2012, 2016 and 2017), we then stop the Sql Server and agent services, zip up the system databases and the mssqlsystemresource database and place them in a folder on the C drive. The normal database backups, which includes system databases, occurs around 8pm. After this, these files are copied over by a network backup process to another location. The C drive is also backed up a this point.

The zipping up and storing the system and resource database in a C drive folder is that that we have a quick bare metal restore option.

This process is something that I have inherited and it seems an unnecessary process, or am I missing something?

Any thoughts on this would be appreciated.

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,759 questions
Accepted answer
  1. Shashank Singh 6,246 Reputation points
    2021-03-02T16:00:08.19+00:00

    This process is something that I have inherited and it seems an unnecessary process, or am I missing something?

    You are correct I do not see any sense in this process. For SP upgrade backup whole system and user databases, store it properly and then do upgrade. Also it goes without saying that you first test SP/CU on your test machine and then move the patch upgrade to prod.
    For SP upgarde refer below

    How-to-install-sql-server-2012-sp2-on-standalone-sql-server-instance.aspx

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-03-02T16:43:26.227+00:00

    I am unclear as to the benefit of backing up just the system databases to a zip file for "bare metal restore". These system databases contain references to the database files and objects, which would not exist on the system in a "bare metal restore" scenario.

    Have you tested this?

    You should always do a full backup before applying patches. However, I will say I have never had to restore a database backup after a patch in 20+ years. The service may not come up after the patch, but that is not due to the databases on the server, it is due to the CODE on the server. And the code/installation must be fixed, not the database. I do remember a problem with one patch, many years ago, which caused an issue on the resource database, but if I remember that was resolved by running a script to fix the resource database, not a restore..

    0 comments
  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-03-03T07:45:38.747+00:00

    Hi RovertJubb-3334,

    I agree with Shashank. The process may not be a good backup solution before applying CU for SQL Server in my view. You can just use it as a fall back.

    Best Regards,
    Amelia

    0 comments
  3. Robert Jubb 46 Reputation points
    2021-03-08T14:04:42.673+00:00

    Hi there,

    Thanks you all for your responses. They have helped. I thought it was me missing something

    Regards

    Robert

    0 comments