Note that for the tempdb and Resource databases, backups are not required at all – in fact, it’s not even possible to back them up.
Read more details on the Microsoft website about which system databases should or should not be backed up, but a quick overview is included below. It is also a good idea to take a backup immediately after any major system-level changes such as adding or deleting users or creating a new database or deleting an existing one.Īlthough the master is the most critical system database, a few of the other system databases are also pretty important and should also be regularly backed up whenever you make any changes that affect them. It is, therefore, important to regularly include the master database in your backups. If the master database is not present or cannot be read, SQL Server cannot start.
The master database also holds information about the other database names, their last state (online, offline/ recovering, etc.), and the location of their data and log files. According to this Microsoft documentation site, the master database “records all of the system-level information for a SQL Server system.” This system-level information includes all the user database logins, linked servers, endpoints, and other system-wide configuration settings. Of these, the master database is the most important. Your configuration may also have an additional one called Configuring Distribution if you have set up database replication, and the server is configured as a replication distributor. There are five main system databases: master, model, msdb, tempdb, and Resource. The system databases are important because they hold important system and database meta-information, without which your SQL Server will not work properly or even start. The SQL Server system databases are system-created and maintained meta-databases that is, they contain data about other databases (in this case, your user-created databases). So should you also back these up? Well, the answer isn’t a simple yes or no … System Databases – What Are They?
But SQL Server also automatically creates and maintains a number of system databases. But by ‘databases’ here, we usually mean your user-created databases. It goes without saying that as a DBA, one of your most important tasks is to schedule and ensure regular backups of your databases.
0 kommentar(er)
