Post reply

Restore a msdb database to a different server - sql server 2008 R2

  • June 28, 2013 at 12:33 pm

    #274617

    Hi, We are currently setting up a new test environment which will be a exact copy of our production server. is it possible to take a backup of msdb database from the source server and restore it to the new destination server? I am thinking of this to import all the jobs from old server to the new one. To my knowledge this can be done as I will have same versions of SQL servers. can somebody help me to understand it better:-).

  • June 28, 2013 at 12:35 pm

    #1628400

    i believe you need to script out the jobs, and find and replace the old server name with the new server name; As I remember it, the jobs have the servername in them, and would attempt to execute them on the named server instead of locally? or am i mis-rembering it?

    i don't think you ever want to restore system databases on other servers, unless it's as a user database for scripting and recovery purposes.

    edit

    i did a bit o' googling, and there a re lots of examples of it being done:, with lots of references right here on SSC too !

    http://www.google.com/search?q=restore+msdb+on+new+server&oq=restore+msdb+on+new+server&sugexp=chrome,mod=16&sourceid=chrome&ie=UTF-8

    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!

  • June 28, 2013 at 12:54 pm

    #1628403

    Another possibility you may want to consider is to use a system tool to ghost the production server and then apply the image to the test server. I've never done it to a SQL Server myself, but my domain admins have taken a ghost image of a server for me before I apply a service pack. This is a "just in case" scenario where if the SP installation goes awry and I can't fix it and I can't restore everything from backup, we have an image that can be reapplied in a few hours.

    I would, however, tend to agree with Lowell. Because we like to be in control of the servers we're responsible for, I would script out all jobs, mail, etc. and then create them on the new server. I've never tried to restore a system database to another server before, so I can't speak to that.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 3 posts - 1 through 2 (of 2 total)

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