none
Restoring database to a different instance name

    Question

  • Hi,

    Case scenario. 

    Restoring from tape media SQL databases (including systems db masters, msdb etc) to another machine (svr02). The source is from another machine (svr01). Both server have different instances name but same SQL server version - 2008. After the restore, the SQL server agent was unable to start. Is this at all possible in the first since the instances are of the different name. Appreciate any reply.

    Thanks.

    Andrew.

    Thursday, April 08, 2010 4:02 PM

All replies

  • What kind of error did you get from SQLAgent?  Did you log in with SSMS or some other tool and reset the SERVER names? 
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 08, 2010 4:04 PM
  • Check SQLAgent.out file in log folder to get the reason for failure. Is SQL installed in the same path on srv02? I think there will some mismatch in subsystem location. However check sqlagent.out file for correct reason.


    Vidhya Sagar. Mark as Answer if it helps!
    Thursday, April 08, 2010 4:26 PM
    Moderator
  • Thanks guys.

    1. Reset server name? I'm more of a server admin role...I assumed this server name is change using T-SQL? What other changes are required - 'instance name'?

    2. The SQL path in different in svr02 as compared with svr01.

    3. Error in SQLAgent.out :

    - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

    - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info', database 'msdb', schema 'dbo'. [SQLSTATE 42000] 

    - ! [298] SQLServer Error: 229, The UPDATE permission was denied on the object 'sysalerts', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DumpAndCheckServerVersion)

    - ! [298] SQLServer Error: 229, The INSERT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. [SQLSTATE 42000] 

    - ! [000] Error creating a new session

    - ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)

    - ? [098] SQLServerAgent terminated (normally).

     

     

    Friday, April 09, 2010 3:06 AM
  • It looks like SQL Agent service account does not have appropriate access. Have a look at the following KB article: http://support.microsoft.com/kb/907739


    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, April 09, 2010 6:27 AM
  • The KB article does not correspond to the error in the log...the SQL version is also different.

    Thanks anyway.

    Friday, April 09, 2010 9:05 AM
  • First problem is restoring SQL System databases across systems.  Not a recommended practice.

    Did you restore the files from tape with SQL turned off or did you RESTORE using the SQL Restore command? 

    I suspect the SQL service and agent accounts do not have appropriate permissions on the newly restored files.  Of course, if you do get SQL to start, then you have a whole host of new problems with your imported SQL system databases.

     


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Friday, April 09, 2010 1:36 PM
    Moderator
  • Thanks Geoff. I do agree with you that restoring across system is not recommended. The 2 servers are setup as database mirroring. Customer wanted to test DR scenario. The restore was via CA arcserve using SQL Agent.

    Friday, April 09, 2010 5:43 PM
  • I have set up Mirroring for DR.  You mirror user databases and you out-of-band synch the rest of the mess.

    I would nuke the target server and rebuild it.  Then I would mirror the user databases (with backup/restore initialization) and script-synch the users.  I would manually recreate or copy the jobs as appropriate.  Restoring MSDB will NOT properly transfer working jobs between servers.

    I will refrain from commenting on ArcServe as we are expected to behave with decorum.

     


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Friday, April 09, 2010 5:59 PM
    Moderator