Restore master to see system tables

Question

Restore master to see system tables

apfousek

SSC Enthusiast

Points: 176
More actions
March 19, 2018 at 12:52 pm

#333952

I'm trying to determine when/if a login was deleted from a development SQL Server (2008 R2). I thought I could restore a backup of the master database from 1 week ago to another database name (master_OLD) and see what logins were on the server last week. Well, the tables/views/procedures (like sys.server_principles) that are visible in master and not there in master_OLD. Obviously, I'm overlooking something about the system architecture within master. Can someone tell me what I'm doing wrong please?

Viewing 14 posts - 1 through 13 (of 13 total)

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

jasona.work SSC Guru Points: 50082 More actions · Answer 1

I'd suspect that, because those are system objects, when you try to query them the server is defaulting to looking in MASTER, not MASTER_Old.

I'd suggest instead trynig to query master_old.sys.[whatever table / view] and see if that does the trick for you.

apfousek SSC Enthusiast Points: 176 More actions · Answer 2

Thanks for your reply - but that was my first guess as well.

select * from master_OLD.sys.server_principals returns the same result as select * from sys.server_principals
I added a new login to the server (after I restored to master_OLD) and ran select * from master_OLD.sys.server_principals and select * from sys.server_principals again. They both returned the new login, so I know they are looking in the same place - which is not what I want.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

Alter the schema on those tables/views to 'dbo', then you should be able to view them just fine.

If it doesn't allow that, you'd have to script them out, change the "sys." to "dbo.", then copy the sys to the dbo.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jasona.work SSC Guru Points: 50082 More actions · Answer 4

apfousek - Monday, March 19, 2018 1:30 PM
Thanks for your reply - but that was my first guess as well.
select * from master_OLD.sys.server_principals returns the same result as select * from sys.server_principals
I added a new login to the server (after I restored to master_OLD) and ran select * from master_OLD.sys.server_principals and select * from sys.server_principals again. They both returned the new login, so I know they are looking in the same place - which is not what I want.

OK, I don't have a SQL 2008 handy, but I just looked in my SQL 2014 Master and noticed that sys.server_principals (and frankly, all the assorted system tables it seems,) are views, not tables. So even in master_old, that view is going to be looking in master which is why you're seeing the results you are.

There is no option to script out the view to see where it might be pulling from.

Possibly at this point, what you might need to try doing is, stand up a new server and install the exact same version of SQL you took your master_old from. Then, try the steps provided here: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql
But make sure you DON'T do this on a server you care about, because I won't promise it'll work, I've never had to try this.
(And, Great Ghu willing, never will)

Sue_H SSC Guru Points: 90860 More actions · Answer 5

apfousek - Monday, March 19, 2018 1:30 PM
Thanks for your reply - but that was my first guess as well.
select * from master_OLD.sys.server_principals returns the same result as select * from sys.server_principals
I added a new login to the server (after I restored to master_OLD) and ran select * from master_OLD.sys.server_principals and select * from sys.server_principals again. They both returned the new login, so I know they are looking in the same place - which is not what I want.

It's pulling from one of the system hidden tables. If you get the definition using object definition:
SELECT OBJECT_DEFINITION(Object_id('sys.server_principals'))

you get the definition of the view and its getting most information from sysxlgns. I've gotten info from those tables before with a restored copy of a different master and connecting with the DAC. Connect with with DAC and see if you can get anything if you execute something like:
SELECT * FROM master_OLD.sys.sysxlgns

I can query the current hidden tables with that - worked with master.sys.sysxlgns. I think it should work with your master_OLD.

Sue

apfousek SSC Enthusiast Points: 176 More actions · Answer 6

Thanks Sue. That sounds promising! I can't try it until the morning though (parental responsibilities) - but I'll let you know how it turns out.

apfousek SSC Enthusiast Points: 176 More actions · Answer 7

I appreciate you helping me think through this. You helped me find the sysxlgns table - and I think Sue (below) has helped me get to a point that I can see the sysxlgns table. I'll report back tomorrow...

Sue_H SSC Guru Points: 90860 More actions · Answer 8

apfousek - Monday, March 19, 2018 2:44 PM
Thanks Sue. That sounds promising! I can't try it until the morning though (parental responsibilities) - but I'll let you know how it turns out.

Thanks a lot for posting back! Please do post back when you get a chance to try it out. I'm real curious but I'm pretty sure it will work for you.

Sue

apfousek SSC Enthusiast Points: 176 More actions · Answer 9

Sue_H - Monday, March 19, 2018 2:55 PM
apfousek - Monday, March 19, 2018 2:44 PM
Thanks Sue. That sounds promising! I can't try it until the morning though (parental responsibilities) - but I'll let you know how it turns out.
Thanks a lot for posting back! Please do post back when you get a chance to try it out. I'm real curious but I'm pretty sure it will work for you.
Sue

Sue. Yes, it worked! I can see master_old.sys.sysxlgns when I log in using DAC. Thank you very much for your help!

Sue_H SSC Guru Points: 90860 More actions · Answer 10

apfousek - Tuesday, March 20, 2018 9:14 AM
Sue. Yes, it worked! I can see master_old.sys.sysxlgns when I log in using DAC. Thank you very much for your help!

You are very welcome - glad that worked for you and thanks for posting back.

Sue

Joe Torre SSChampion Points: 10248 More actions · Answer 11

BTW
EXEC sp_helptext 'sys.server_principals'
returns:

Love me some sp_helptext

Sue_H SSC Guru Points: 90860 More actions · Answer 12

Joe Torre - Tuesday, March 20, 2018 2:38 PM
BTW
EXEC sp_helptext 'sys.server_principals'
returns:

Love me some sp_helptext

Do a search on sp_helptext vs object_definition - quite a few articles. That's pretty much why I've been getting out of the habit of sp_helptext and using object_defiinition.
So now I love me some object_definition.

Sue

Joe Torre SSChampion Points: 10248 More actions · Answer 13

Joe Torre

SSChampion

Points: 10248

March 20, 2018 at 3:58 pm

#1984069

Thanks Sue