I have a live db A and a test db B. The live db is backup'd every day. And I use A's backup file to restore to B to keep in sync. But then B's permission setup is also overwritten by A's. Can I only restore A's data but keep B's permission?

  • 1
    Too bad your test DB couldn't be put in read-only mode and then that'd help ensure no knew changes or data can be applied... I've always had to put the logic in the restore portions of the TSQL that does the DB restore to complete the restore, and then set the elevated permissions explicitly for the test DB where those need to be elevated on test where they don't have this level of permission in production.... I've felt this pain before... lol... get to TSQL scripting the logic as Ali suggests as that's really the best method to keep the backup/restore procedure as-is. – IT Thug Ninja Dec 27 '16 at 1:18

A SQL Server database backup is the whole database, users included. You can script out the permissions prior and reapply them as part of your process while blowing out the other permissions. You could make your db schema work on both environments. You could also just do data exports and load up data. Tools like RedGate Data Compare could also help keep the data in sync.

If you want to give more info about your environment we can help guide you to the best option but you have several.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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