Post reply

Restoring a database

  • July 9, 2010 at 4:03 am

    #139634

    Hi,

    Forgive me if this has been answered before, or if I maybe going about this the wrong way. I would like to restore a database, but leave out the security folder, which contains the Users, Roles and Schemas. I want to leave just the security folder as it is for the database that I am over-writing, but copy over everything else.

    I'm not even sure if this is possible, because it might be constrained by some of the rights of the original database.

    The reason for this is related to Reporting Services, I cant seem to create a user on a database that has the correct rights for reporting services. Apparently reporting services uses a default account called RSExecRole, but each time I create this new role, I still cannot get reporting services to communicate with the new database.

    Any help in this matter is greatly appreciated.

    Thanks

    Torik

  • July 9, 2010 at 6:20 am

    #1190740

    Toric what you want to do is to look in the scripts section here on SSC, and find some of the examples that script out roles and users, and their permissions.

    use those scripts to dump all those things out to a file.

    then you can restore the database, and then run the script to add your "correct" roles/users/settings back.

    you could also add a script to delete any roles and users BEFORE you run your script so it is exactly the way you wanted it.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 9, 2010 at 6:20 am

    #1190741

    Cannot be done as part of the restore. Restore recreates the database as it was at the time it was backed up. You can run scripts afterwards to drop users, but not as part of the restore process.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2010 at 9:01 am

    #1190848

    Thanks v much for your reply guys, I will try Lowells method first and let you know how I get on.

    Cheers

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply