Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server).

The databases themselves will be transferred using a database backup and restore script. I have tested this fine and it works without problems.

My worry is rather regarding all the other instance/system objects, like Logins, SQL Agent jobs. These have been scripted out from the source server and reapplied on the new server, and I'm afraid I have forgot some. I can obviously visually compare using SQL Management Studio, which is what I did, but it is time-consuming and error-prone. So, before plugging the plug off the existing server in due time, I would prefer to have a more "scientific" way to confirm that everything is in there...

I've looked for software doing this kind of comparison and have found only schema or data compare tools. I guess I could compare the system tables like sys.sql_logins and msdb.dbo.sysjobs, etc, but I don't know them all.

In short, is there a way to quickly compare the instance-level objects of two servers, and if possible, their contents as well rather than just by name?

Thank you.

share|improve this question

2 Answers 2

If you're only concerned about Agent Jobs and logins, I think the easiest approach is to leverage a linked server and querying the relative tables. I would do this by first creating a linked server on the old instance that points at the new instance. Once I had that, I could run the following queries:

Logins

SELECT * FROM [SQL2005].[master].[sys].[server_principals]
WHERE name NOT IN (SELECT name FROM [SQL2008].[master].[sys].[server_principals])

Agent Jobs

SELECT * FROM [SQL2005].[msdb].[dbo].[sysjobs]
WHERE name NOT IN (SELECT name FROM [SQL2008].[msdb].[dbo].[sysjobs])

There might be some inconsistencies with some server specific accounts/logins, but these queries should show you everything that exists in the old server that doesn't exist in the new server. You should be able to extend this logic to other facets as long as you had system tables to query for it.

As for objects that need to be synchronized, I think the best approach is to review your instance for the objects that should be migrated. Typically this would only be logins and Agent Jobs, but could also include linked servers (sys.servers), end points (sys.endpoints), and other objects.

share|improve this answer

I believe that if you transfer the system databases to your new instance you will retain the system level objects you are referring to. This MSDN article should help (as suggested from a similar question posted here): http://msdn.microsoft.com/en-us/library/ms345408.aspx

There is also an article about transferring logins between instances: http://support.microsoft.com/kb/918992

share|improve this answer
2  
Thanks, but I've read that migrating the system databases was not a best practice, especially with different SQL Server versions as in my case (2005 -> 2008 R2). –  Kharlos Dominguez Oct 28 '12 at 20:52
    
This would not be a good idea. If its moving to a shared instance copying over the system data bases would change configuration options, as well as issues with agent jobs, logins, and even other databases on the server. –  Brandon leach Dec 27 '12 at 20:37
    
This would not even work for the master database since the OP is going from SQL 2005 to SQL 2008. –  Shawn Melton Feb 25 '13 at 22:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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