Blog Post

SQL Homework – November 2021 – Backup, restore, and move the system databases?

,

On any instance you have at least 5 system databases. Master, model, msdb, tempdb and one other. First 5 points for this homework. What is the other system database that is required on any instance?

I’m assuming that when you take backups you are making a point of backing up your system databases. If not, then you’d best start. Here’s what I’d like you to spend some time doing this month.

  • Create a test instance that you won’t be upset if it gets fried.
  • Back up your system databases.
  • Recover a system database (15 pts each) (x5, only the required system databases)
    • Stop your instance
    • Delete the files for one of the system databases.
    • Get your instance back up and running.
  • Move a system database (5 pts each) (x4, only the required, visible system databases)
    • This could be a different directory, or a different drive. The only rule is that when you are done none of your system databases are in the original location.
    • It’s highly recommended that you don’t move that other database. Why not? (5 bonus points)

Note: This is one of those homeworks that’s really important. Make sure that you know how to do this. In case it wasn’t obvious, what we are doing is practicing in case of a catastrophic failure of one of the system databases, or the drive where the system databases exists.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating