How to backup the SQL Server resource itself not the databases?

Question

Pretty basic scenario, someone deletes a SQL Server by accident or maliciously, doesn't matter which. What happens to all of my databases? What about all of the settings in the SQL Server resource (allowed networks, etc.)?

How do I backup this SQL Server itself not just my databases?

Answer 1

@Ryan Tracey

Welcome to Microsoft Q&A, thank you for posting your question here!

How you can backup SQL Server itself not just database depends on few criteria:

1. You can back up all the SQL Server configurations and resource by backing up your Operating System (OS) state.
2. You can create a virtual disk copy of the database server or system. Which can be re-use as the original system or server.
3. Also, the Resource database resides in the mssqlsystemresource.mdf file for example, notice .mdf file which contains only code and settings. Therefore, SQL Server cannot back up the Resource database. Note: You can perform a file-based or a disk-based backup on the mssqlsystemresource.mdf file by treating the file as if it were a binary (.exe) file, instead of a database file.
4. You can copy your .mdf file and keep somewhere external out of the system or SQL Server (System).

Hope this is helpful.

Kindly let me know if the above helps or you need further assistance or explanation on this issue.

Best Regards,

Sina

Answer 2

Hi @Ryan Tracey,

Please refer to this MS document: Backup & restore: system databases (SQL Server)

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server?view=sql-server-ver16

SQL Server maintains a set of system-level databases, system databases, which are essential for the operation of a server instance. Several of the system databases must be backed up after every significant update. The system databases that you must always back up include msdb, master, and model. If any database uses replication on the server instance, there is a distribution system database that you must also back up. tempdb is re-created every time an instance of SQL Server is started.

There is no way to do backups directly from the instance level. By doing good backup of your system databases you can prevent disk corruption.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Thanks for your questions. 

Here is a thread on how to recover Azure SQL Server in case if it's deleted by mistake 

Once the server recovered, you will find all databases in the “Deleted databases” which you would need to recover by following steps mentioned on the document 

https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql&tabs=azure-portal#deleted-database-restore 

You can restore a deleted database to the deletion time, or an earlier point in time, on the same server by using the Azure portal, the Azure CLI, Azure PowerShell, and the REST API. 

Let us if this helps or if you have any further questions.

Answer 4

most of the sqlserver settings are in the master database. you should keep a current backup. other settings are in the registry.

your best bet is to use install scripts rather than a GUI to install and configure sqlserver. this makes it a repeatable process.