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).