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…)
Plans will be recreated at first run.
Otherwise, everything database level will be there except where it interacts at the instance level, such as:
- 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).