Cannot login after SIMS SQL restore
I have beautifully crafted backup and restore policy for my SIMS database. The database is in Full Backup mode and I have a Maintenance plan that runs a weekly full backup, daily differential backups and hourly transaction log backups. The Weekly and Daily plans also run the T-SQL command "exec sims.sims.db_p_transfer_login" before the backup routine to ensure that the logins are moved into the sims database. These run successfully without any reported errors.
During disaster recovery testing, I can successfully use the backups to restore the database to a point in time of my choosing, which is very useful. However, the login details are not transferred so I cannot log into SIMS once it has been restored. I have run the sims.sims.db_p_transfer_login script after the restore to see if this made a difference, it didn't. The users are all sat there in the sims/Security/Users folder (Server Management Studio), but not achieving anything currently.
Turns out I have a beautifully crafted chocolate fireguard......can anyone out there offer any assistance on where I have gone wrong?
I don't wish to return to the scripted dbattach backup system in SQL Simple Backup mode that was running before (I lost all logins once before doing it that way). I am also reluctant to detach and re-attach the live database as I wouldn't be able to do this in a disaster recovery scenario.