July 13, 2015 at 9:12 am
Hi all.
I have the following problem. I need to investigate about what happened to our production server at the last weekend. Thankfully, we have a backup from last Friday, and I restored it to another server which is development. I restored it under a name "old_master_2015_07_10".
But if I run a query
SELECT *
FROM [old_master_2015_07_10].[sys].[servers]
it actually selects from master.sys.servers, not from my old_master... In order to prove it, I created a linked server in this, development server, and if I run SELECT * FROM [old_master_2015_07_10].[sys].[servers], it selects it. And in database selection drop-down box I also selected old_master_2015_07_10.What I think it apparently recognizes familiar names like sys.servers and redirects the query to the master.
What I can do to select really from old_master_2015_07_10 database? I already thought about renaming sys.servers to something different, but did not do it not to break something in master in case if SQL Server will run it in master as well.
Thanks
July 13, 2015 at 9:27 am
I've a feeling you need to restore the resource database on the development server as well. Make sure you make a copy of the existing resource database before you overwrite it, so you can put it back to how it was.
John
July 13, 2015 at 9:39 am
That's interesting idea, thank you.
But if I try to :
backup database [Resource]
to disk = 'i:\backup\resource.bak'
I get the following error:
Msg 911, Level 16, State 11, Line 2
Database 'Resource' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
July 13, 2015 at 9:42 am
That's covered in the link in my previous post.
John
July 13, 2015 at 10:05 am
Yes, I just also found it. So it does not look as a solution?
July 13, 2015 at 10:21 am
I'm not sure restoring the resource database will actually do what you need. sys.servers, like other such system objects, is just a view that is logically available in the sys schema of every DB, but physically stored in the resource DB (also mentioned in the above link).
In the case of sys.servers, it's hitting a system base table in the master database. That's why it doesn't matter in what DB you're referencing sys.servers; it's just a view hitting a base table in master regardless. That's also why bringing over the resource DB wouldn't help. It has the definition of the view, but the data is in the actual master DB for the instance.
I'm not sure if you were just using it as an example, but if you want to see what was in the base table used for sys.servers, then you already have what you need in the restored copy of master.
To see that base table, though, you'll need to connect using the DAC. Then you can query [old_master_2015_07_10].[sys].[sysxsrvs] (sysxsrvs is the base table in master).
Cheers!
July 13, 2015 at 2:59 pm
Tried to connect with DAC, but encountered problems.
BOL states that "The connection is only allowed from a client running on the server. No network connections are permitted."
What that means? that I have to physically connect only from SSMS on that server? Can I RDP to it and run SSMS from there? Or it considers it as "through the network" anyway?
I am member of sysadmin group, and typed ADMIN:server-name in connection box. But probably the difficulty is that our server name contains a dash like in my example above, so I tried to surround it with the brackets like this - ADMIN:[server-name], but it does not connect anyway.
July 13, 2015 at 3:11 pm
Yes, you can RDP to the machine and use the client there. That's just the default behavior, though. You can allow remote admin connections by changing the configuration option of the same name.
A dash in the name would not prevent you from connecting. If you're having issues connecting, then most likely either 1) you're trying to connect remotely and remote admin connections is not enabled, or 2) you're trying to connect object explorer in SSMS, which won't be allowed because it tries to open multiple connections (instead, just connect a query window, not the object explorer; alternatively, don't use SSMS at all and use sqlcmd).
What was the exact error you received when attempting to connect?
Cheers!
July 13, 2015 at 3:24 pm
Thanks, this was my mistake:
2) you're trying to connect object explorer in SSMS, which won't be allowed
Now I will go further, because this was holding me.
July 13, 2015 at 4:03 pm
Thanks again, now I can easily query :
select * from [old_master_2015_07_10].[sys].[sysxsrvs]
Now I need an underlyer table for sys.server_principals view. But how to find all the tables for system views? I wanted to script out a system view (there is no such option in SSMS), tried sys.system_objects.
Is there a list for all of them?
Thanks in advance.
July 13, 2015 at 4:11 pm
Try sp_helptext, as in:
EXEC master.sys.sp_helptext 'sys.server_principals'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 13, 2015 at 4:20 pm
Just use this: SELECT OBJECT_DEFINITION(OBJECT_ID('sys.server_principals'))
--Or whatever system view you're interested in
Just be warned that even connected to the DAC you won't always be able to run the underlying queries without modification. There are also some special functions that are used in those views that even when using the DAC won't work.
At any rate, that will show you the base tables being used. If I may ask, what exactly are you trying to pull from the restored version of master?
Cheers!
EDIT: While I was typing mine, Scott posted his response. His way will also work 🙂
July 14, 2015 at 7:39 am
At any rate, that will show you the base tables being used. If I may ask, what exactly are you trying to pull from the restored version of master?
At the last weekend our system team ran some maintenance on our production server, like something related to storage and something else. As a result of it (I really don't know how this caould have happened) some logins, users, AD groups and linked servers have been dropped.
So I decided to use a backup of master database that has been backed up some time prior to that maintenance, and to figure out all these settings from there. But I needed to restore it to another server, and with different name, not to interfere with that master.
And both yours and Scott's queries return exactly the same results. Thank both of you.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply