What is not persisted in a database backup / restore? (SQL Server 2005)

Posted on

Question :

If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like cached execution plans, statistics etc…)

Answer :

Plans will be recreated at first run.

Otherwise, everything database level will be there except where it interacts at the instance level, such as:

  • replication
  • backup jobs/schedules
  • maintenance plans/schedules
  • login mappings to users

For a standard, everyday database, you’ve no worries.

Orphaned Users are often the result of restoring MS SQL Server databases. Here’s an article on fixing orphaned users.

I am not sure if I have been doing it incorrectly or not, however, when I do a restore it never seems to carry over user information. I end up needed to re add them manually (or through another script).

Leave a Reply

Your email address will not be published. Required fields are marked *