September 24, 2008 at 12:49 pm
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
October 6, 2008 at 11:48 pm
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?
December 28, 2008 at 12:52 am
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.
December 28, 2008 at 7:59 pm
I'm glad that someone benefited .. you are welcome. That's why I love this community.
January 16, 2009 at 9:37 am
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.
April 22, 2009 at 8:22 am
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.
May 22, 2009 at 1:33 am
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.
May 24, 2009 at 9:01 am
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
May 24, 2009 at 10:01 am
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.
September 30, 2009 at 12:03 pm
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
September 30, 2009 at 1:15 pm
Check the Windows Event log for the reason the service won't start.
September 30, 2009 at 1:26 pm
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...
September 30, 2009 at 3:35 pm
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.
September 30, 2009 at 3:38 pm
The location for the master db is a parameter in services. You can fix it for the service.
September 30, 2009 at 3:38 pm
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