question

RobertJubb-3334 avatar image
0 Votes"
RobertJubb-3334 asked ·

Backup of of mssqlsystemresource

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-general
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi RovertJubb-3334,
Was your issue resolved?
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
Shashank-Singh avatar image
1 Vote"
Shashank-Singh answered ·

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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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..

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RobertJubb-3334 avatar image
0 Votes"
RobertJubb-3334 answered ·

Hi there,

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

Regards

Robert

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.