Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am looking to setup a maintenance plan that makes a backup of a database (Database1) and then restores to another database (Database2) to confirm that it is able to restore successfully.

Can I do this as part of a maintenance plan and have SQL server mail me if it fails either the backup or the restore?

share|improve this question

migrated from Oct 19 '12 at 11:18

This question came from our site for professional and enthusiast programmers.

Do you need Database2 to exist afterwards? There's an option to verify the backup, which actually runs a restore (but doesn't leave a database behind) – Damien_The_Unbeliever Oct 19 '12 at 6:57
The database isn't actually used by anything. I just need to confirm that the restore is possible. – William Hurst Oct 19 '12 at 12:13

Yes you can. After your backup task, add an Execute TSQL task that has the restore command. Join them together with a success constraint.

Add a Send Mail task and join it to both the backup task and the restore task. Make it an OR constraint and convert it to a failure constraint rather than success

share|improve this answer

After update you have to fix orphaned users


EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

for each user that access the database or you'll have errors in login from external applications.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.