Tell me more ×
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. It's 100% free, no registration required.
up vote 0 down vote favorite

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

migrated from stackoverflow.com Oct 19 '12 at 11:18

2 Answers

up vote 1 down vote

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
up vote 0 down vote

After update you have to fix orphaned users

with

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

 
discard

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.