SQL Server 2005 Master database Restore to a Different Server

  • Hi guys,

    just a bone head question by the way , when we talking about using alter

    database command to point the logical name to the mdf ab dldf. are we talking about giving the path to the mdl and fld files??

    Thanks

    Bobby

  • Just a few quick questions,

    Please correct me if I am wrong, but master db contain login information, so essentially you are just copying the logins to the target server, just like how microsoft sp_help_revlogin script, right?

    Also what do the other system db contain, jobs, alerts, etc?

  • raymondpe (4/26/2008)


    ok this worked for me..... just in case anyone has to do this:

    > RESTORE DATABASE master FROM DISK = 'C:\MINIDR\masterbackupfile.bak' WITH

    RECOVERY, REPLACE;

    2> GO

    Problem solved.

    Hi Raymond,

    I had same problem today. Your post saved me.

    I am thankful to you.

  • I'm glad that someone benefited .. you are welcome. That's why I love this community.

  • Hi i just need to move all system files from C to D

    Is this Step 7

    Is where i put master.mdf? insteald of myssqlsystemsource?

    Where do you put D:\ PATH

    ALTER DATABASE mssqlsystemresource

    MODIFY FILE (name = data, filename = ' \mssqlsystemresource.mdf') (is this master.mdf) ?

    GO

    Once done master you stop server and restart again in single mode (

    this with the T3608) before you do the other msdb, model.

    --

    Also how to use sqlcmd to connect to sql server thanks.

    source server = server from which backups originated

    target server = server onto which we wish to restore the system databases

    Presuming that paths to system files are different (more difficult to do than if paths are the same)

    1. Ensure target server is same build revision as source server. Patch accordingly.

    2. Start target server in single user mode (sqlservr -c -m -f)

    3. Connect to SQL Server using sqlcmd

    4. Restore master database (Note - does not require WITH MOVE option).

    When done, SQL Server stops automatically.

    5. Start target server in single user mode (sqlservr -c -m -f -T3608)

    6. Connect to SQL Server using sqlcmd

    7. Use ALTER DATABASE command to point SQL Server to the mssqlsystemresource database:

    ALTER DATABASE mssqlsystemresource

    MODIFY FILE (name = data, filename = ' \mssqlsystemresource.mdf') (is this master.mdf) ?

    GO

    ALTER DATABASE mssqlsystemresource

    MODIFY FILE (name = log, filename = ' \mssqlsystemresource.ldf')

    GO

    8. Stop SQL Server (Ctrl-C). Start in single user mode.

    9. Use ALTER DATABASE command to point SQL Server to the model, msdb & tempdb databases:

    modeldev = model.mdf

    modellog = modellog.ldf

    msdbdata = msdbdata.mdf

    msdblog = msdblog.ldf

    tempdev = tempdb.mdf

    templog = templog.ldf

    10. Stop SQL Server. Start SQL Server (either from cmd or as service).

    11. Restore msdb, model if required.

  • Thanks a lot for your post Scott. I'm performing a multi step upgrade from 32bit SQL 2000 to 64bit SQL 2005. I first perform an in place upgrade of SQL 2005 on the 32bit side to preserve logins and then use the backup and restore master method on a cleanly built SQL 2005 64bit box. The system database paths did change, and your ALTER DATABASE commands did the trick to fix them. One piece of advice for anyone working with Binary sort order, hence case sensitivity, I had to capitalize my msdb file names to "MSDBData" and "MSDBLog" respectively.

  • Hello guys,

    im new here in the forum, i would like to ask for help. i'm also a newbie when it comes to database administration...

    Scenario:

    im trying to restore the master database from ms sql 2000 to ms sql 2005. i was not successful when i attempted the procedure from microsoft. all i got was an error message

    "The backup of the system database on the device "device" cannot be resored because it was created by a different version of the server (8.00.0194) that this server (9.00.1399) Msg 3013, level 16 state 1, Server TESTBED, Line 1. RESTORE DATABASE is terminating abnornally."

    What do i have to do so i will be able to resore the master database.

    Hope you can help me.

    Thank you in advance.

  • I don't think you can do master from sql 2000 to sql 2005..only version to version and same service pack.

    If you need all the logins look up sp_help_revlogin and run on sql 2000 to copy all the logins over to sql 2005

  • You cannot restore master to another version. even another patch level can be asking for problems. Master contains version and instance specific information. You must restore to another 2000 instance.

    As mentioned by Tracey above, you can move the logins by scripting them out.

  • I can't start sql server services after step 4 in scott Duncans suggestion. Restore was successful. When i tried starting it says

    "The MSSQLSERVER service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service."

    --------------------------------------

    source server = server from which backups originated

    target server = server onto which we wish to restore the system databases

    Presuming that paths to system files are different (more difficult to do than if paths are the same)

    1. Ensure target server is same build revision as source server. Patch accordingly.

    2. Start target server in single user mode (sqlservr -c -m -f)

    3. Connect to SQL Server using sqlcmd

    4. Restore master database (Note - does not require WITH MOVE option).

    When done, SQL Server stops automatically.

    5. Start target server in single user mode (sqlservr -c -m -f -T3608)

    6. Connect to SQL Server using sqlcmd

    7. Use ALTER DATABASE command to point SQL Server to the mssqlsystemresource database:

    ALTER DATABASE mssqlsystemresource

    MODIFY FILE (name = data, filename = ' \mssqlsystemresource.mdf')

    GO

    ALTER DATABASE mssqlsystemresource

    MODIFY FILE (name = log, filename = ' \mssqlsystemresource.ldf')

    GO

    8. Stop SQL Server (Ctrl-C). Start in single user mode.

    9. Use ALTER DATABASE command to point SQL Server to the model, msdb & tempdb databases:

    modeldev = model.mdf

    modellog = modellog.ldf

    msdbdata = msdbdata.mdf

    msdblog = msdblog.ldf

    tempdev = tempdb.mdf

    templog = templog.ldf

    10. Stop SQL Server. Start SQL Server (either from cmd or as service).

    11. Restore msdb, model if required.

    --------------------------------------------------------------------------------

    Scott Duncan

  • Check the Windows Event log for the reason the service won't start.

  • Since source and target server have different file location. This is what i got error. Resource is still pointing to old locatio. What can i do to resolve this? Master can't locate resource database after restore

    Event Type:Error

    Event Source:MSSQLSERVER

    Event Category:(2)

    Event ID:17207

    Date:9/30/2009

    Time:11:38:21 AM

    User:N/A

    Computer:sql

    Description:

    The description for Event ID ( 17207 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: FileMgr::StartLogFiles, 2(The system cannot find the file specified.), H:\MSSQL9\MSSQL.1\MSSQL\DATA\mssqlsystemresource.ldf.

    Data:

    0000: 37 43 00 00 10 00 00 00 7C......

    0008: 0b 00 00 00 44 00 45 00 ....D.E.

    0010: 56 00 54 00 45 00 53 00 V.T.E.S.

    0018: 54 00 53 00 51 00 4c 00 T.S.Q.L.

    0020: 00 00 07 00 00 00 6d 00 ......m.

    0028: 61 00 73 00 74 00 65 00 a.s.t.e.

    0030: 72 00 00 00 r...

  • Is it possible to restore the master to a different server on the same network. For example in a BMR exercise? The target server will have a different name then the source server.

  • The location for the master db is a parameter in services. You can fix it for the service.

  • If you restore master to a different windows server, at first it will not be visible on the network. It will be responding to the old name. sp_dropserver, sp_addserver will fix it.

Viewing 15 posts - 16 through 30 (of 35 total)

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