March 5, 2008 at 12:32 pm
Hi!
I'm trying to launch SQL 2005 in a single user mode so I can restore the master DB. When I open up the Command line, I run:
d:program files\microsoft sql server\mssql\mssql1.0\binn>sqlservr.exe -m
And then nothing. It just gives me another line prompt of
d:program files\microsoft sql server\mssql\mssql1.0\binn>
When I go to the GUI and double click sqlserver executable, it also does nothing. SQL Server functions perfectly well on its own, of course. Could this be because we have it on the D: Drive? Any ideas?
Thanks Everyone!
March 5, 2008 at 1:09 pm
You need to start in minimal config mode (/f) and skip recovery on all db's except master (/T3608).
Basically, stop SQL and then
NET START MSSQLSERVER /f /T3608
March 5, 2008 at 1:22 pm
Oh whoops, I just read again ... you're trying to restore, not move ...
Can't say I've ever had to restore the master db, I've rebuilt, but not restored. Out of curiosity, why are you restoring the master database?
Anyways, yeah, the -m flag is what you would need ...
NET START MSSQLSERVER /m
March 5, 2008 at 1:32 pm
Adam Bean (3/5/2008)
Oh whoops, I just read again ... you're trying to restore, not move ...Can't say I've ever had to restore the master db, I've rebuilt, but not restored. Out of curiosity, why are you restoring the master database?
NET START MSSQLSERVER /m
We're moving data centers, and we're just restoring the master database to our new instance of SQL 2005....unless that is for some reason unnecessary, as I'm restoring all the other db's along with it?
March 5, 2008 at 1:38 pm
bdragoo (3/5/2008)
We're moving data centers, and we're just restoring the master database to our new instance of SQL 2005....unless that is for some reason unnecessary, as I'm restoring all the other db's along with it?
I can't say I've personally done that (restore the system db's), but from past experience, I would never do such a thing. Put the time into scripting out any appropriate logins [with their sids, or just repair orphan users later] (master) and jobs/maintenance plans/operators/etc. (msdb) and simply migrate only your user databases. I've migrated our production/qa/dev environments probably at least a dozen times to continue to improve hardware and such.
I would wait for some additional feedback, but as stated, I personally would prefer to start fresh with new system databases than to run the risk of trying to restore system databases which have server/instance specific settings that will probably end up causing more of a headache in the long run.
March 5, 2008 at 1:47 pm
Something to note (I'd go look for white pages on this to confirm), but I do not believe you can restore the master db in 2005 anymore with the dependency on the mssqlsystemresource database. Keep that in mind if you decide to take this path, that you'll have to take that database with you as well (which can not be backed up).
March 5, 2008 at 3:20 pm
I tried rolling through what you outlined, then realized it was going to be much easier to re-script the master databases. Somehow in the course of me doing this, I shut down the database, and cannot get it to restart again. I guess I'll have a little more work in front of me now:blink:
March 5, 2008 at 3:59 pm
I've restored the system dbs from one SQL 2005 server to another (don't ask me why, I can't remember. Pretty sure it was more than just to see if it could be done).
Fortunately, I documented what I did. Not much different to restore to the same server, so give this a whirl:
Restore system databases from one server to another
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.
Good luck.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply