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


November 2, 2021 by Kenneth Fisher

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.

2 thoughts on “SQL Homework – November 2021 – Backup, restore, and move the system databases.

  1. Anna DBA says:

    Q1: Resource database.
    Comment: Agree! This is our best preparation for system recovery in case of corruption of system database(s). SQL would not start without them.
    Advice: Do this entire exercise in production 🙂 (Please do not!)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,746 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: