Post reply

Identify DB Owner from Backup File?

  • October 24, 2008 at 9:36 am

    #131799

    Hi there...

    I am curious if there is a way to explore the backup container / file to find out what login is the DB Owner...

    Reason I ask is I am writing a script to restore a test server and the production databases have different owners...

    If I restore them under the sa login or my login it will break some applications where the db owner needs to be different...

    I googled the above question and looked in BOL but didn't really find anything specific...

    I am specifically looking for a way to identify the db owner from the backup file...nothing in a system table or anything like that.

    Any info is appreciated.

    Thanks in advance,

    Lee

  • October 24, 2008 at 10:28 am

    #889343

    i looked at the 4 special RESTORE commands that I know of in BOL:

    RESTORE HEADERONLY

    RESTORE FILELISTONLY

    RESTORE LABELONLY

    RESTORE VERIFYONLY

    looks like you can find the name of the user that rant he actual backup, but not the list of users IN the database, and thus their role/permissions.

    only way i see is to restore the database, and then analyze it, which was obviously not what you wanted to here.

    maybe you could make a job which writes a separate file for each backup which has the info you need, and then performs the backup....that way it could tag along with your backup file itself?

    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!

  • October 24, 2008 at 10:36 am

    #889351

    I don't think any of the data in the backup file is visible natively. SQLCompare (Red Gate) can do this, comparing with a backup file and can likely pull the information. Haven't looked at Litespeed, but it might work as well.

  • October 24, 2008 at 10:36 am

    #889352

    Lowell (10/24/2008)

    i looked at the 4 special RESTORE commands that I know of in BOL:

    RESTORE HEADERONLY

    RESTORE FILELISTONLY

    RESTORE LABELONLY

    RESTORE VERIFYONLY

    looks like you can find the name of the user that rant he actual backup, but not the list of users IN the database, and thus their role/permissions.

    only way i see is to restore the database, and then analyze it, which was obviously not what you wanted to here.

    maybe you could make a job which writes a separate file for each backup which has the info you need, and then performs the backup....that way it could tag along with your backup file itself?

    Hi Lowell,

    I found the same thing and I am actually using two of those SQL commands to build the restore commands in my script...

    I was hoping to keep it all in one script and to not have multiple steps...

    My project is one where I have to refresh 18 separate test environments within a 24 hour period to keep them in sync, so any kind of manual intervention is not something I am looking for...

    My backup plan was basically gonna wait until people yelled something is broke before I investigate the db_owner...broken logins...ect...

    I was hoping to streamline the restore process and db owner assigning in one swoop...but I don't think I am going to be able to do that from the backup file.:angry:

  • October 24, 2008 at 2:35 pm

    #889481

    I don't think you would be able to get that information from a backup as its not held in the database itself but in master..sysdatabases.

    So perhaps you are stuck with a seperate script going back to the prod box via a linked server and running something like -

    select 'exec sp_changedbowner '+"'"+suser_sname(sid)+"'" from prod.master..sysdatabases where name= @dbname

    ---------------------------------------------------------------------

  • October 24, 2008 at 2:43 pm

    #889485

    george sibbald (10/24/2008)

    I don't think you would be able to get that information from a backup as its not held in the database itself but in master..sysdatabases.

    So perhaps you are stuck with a seperate script going back to the prod box via a linked server and running something like -

    select 'exec sp_changedbowner '+"'"+suser_sname(sid)+"'" from prod.master..sysdatabases where name= @dbname

    Thanks for the post and I believe you are right...I thought about it more and I think this is the easiest way...and you even saved me the time of writing the script!

    Thanks guys!

    Lee

  • October 24, 2008 at 2:53 pm

    #889491

    Lee,

    no probs. Not quite written script, if you want it automated, you'll have to put that in a variable and exec @variable

    have fun

    george

    ---------------------------------------------------------------------

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

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