Problem while restoring master database
-
I have to restore 'master' database in sql server 2005. The server has two instances. As part of restore strategy, I need to bring server in single user mode. When I ran following in command prompt
sqlservr.exe - m -s via Binn Folder I got following error message.
Your SQL Server installation is either corrupt or has been tampered with (SQL Registery version not same as server). Please uninstall then re-run setup to correct this problem.
I have been clueless how to resolve this problem. Your help will be appreciated.
Mike
-
Something tampering has happend to your config file of sql installation during the intial stage. As the error itself suggested, better un-install & re-install the sql 2005...
-
Hi
You mean to say you want to bring the db in single user mode?
"Keep Trying"
-
No i did'nt mean it that way. Since, u have n number of connections to hit the db, I thought of stopping the server for a while. un-install the server components and re-install the same.
-
Hi
My comment "You mean to say you want to bring the db in single user mode? " was intended for Maddy. 🙂
"Keep Trying"
-
I guess my first question might be why do you need to restore the master DB?
In general the only things that I find acceptable for the master database are shared procedures (some times) and Maintenance routines/objects. I hope you didn't develop an application in the master DB.
-
Instead a command line, try to put -m on service.
Stop SQL Server service, open properties for the services, in start parameters put -m, try to start.
Try to restore.
When restore is finish, sql raise an unexpected error and connection with sql server is stolen.
Press ok, don’t have any option
Review services console, refresh the screen, as you should see the SQL server service is stopped.
Open it properties, delete –m option.
Start normally the service.
Open Sql Server Management Studio and review
Restore normally msdb database and review that all jobs are successfully restored.
Francisco Racionero
twitter: @fracionero -
Maddy,
Why do you think you need to restore master. This is something you should plan to avoid. Please let us know your situation, as some of us may be able to suggest ways to maybe avoid this restore, and hopefully avoid the need to restore master in the future.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
This doesn't work. You can stop the SQL 2005 services but you cannot change the way they start up
-
Hi All
This is my problem. I have installed a brand new copy of SQL 2005 standard edition onto a Windows 2003 Stnadard server. The install went into e:\program files\microsoft SQL Server 2005 (I specified the path on install)
I now have another SQL 2005 instance on another machine and i made a master database backup from the SQL server. (The brand new install above is a copy of the production server, and the master database backup was taken from the production database)
Note: There is also a SQL 2000 SP4 instance on the production and brand new PC (on the production server , both instances are running together with no problems)
When i try start the brand new SQL 2005 server in single user mode sqlservr -m i get the following
Both production and brand new install do NOT have SP's on them, and i DON'T want to put service packs on them because then they wouldn't be the same (meaning SQL 2005 not SQL 2000)
This is what i have tried
On the brand new install i tried running sqlservr -m SQLServer2005 (that didn't work, same error as attached picture)
I also tried sqlservr -m (computername)\SQLserver2005 (that didn't work, same error as attached picture)
I also tried sqlservr -d (specified path to master database file) -l (specified path to master database log file), (that didn't work, same error as attached picture)
I have tried in add and remove programs to repair the parts of SQL that have the repair option, (no luck)
I have tried uninstalling and reinstalling SQL as it specifies in the picture (also with no positive outcomes)
What the heck is wrong with a BRAND new install of SQL 2005 that causes this to happen???
Hoping someone has some ideas?
Thanks in advance
-
Are the instances of SQL Server on each of the servers installed into the same folders? For one of them, you mentioned you installed to E:\.... What about the other? This can cause issue with the mssqlsystemresource database.
I recently had need to restore a master database from one server onto another, with the SQL instance on the second server being installed on a different drive/folder. Here are the notes I made:
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) from command line
3. Connect to target 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 normally (either from cmd or as service).
11. Restore msdb, model if required.
Scott DuncanMARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare -
I could not see the error image because is was either stripped or has a bad link. Your problem sounds like a binary issue. While restoring a Master DB is not the optimal solution, you should safeguard yourself by backing up the Master database before restoring, even on a new install.
Scotts instruction should take care of you. If you want to try to get into DAC another way, you connect to Admin:Server\Instance.
-Adam
-
Hi there Scott
What i eventually did, which seemed to work and keep all settings and security the same was to scripts out just the user permissions from the old SQL2005 server by right clicking on the master database and selecting script as or out or something like that and then go through the wizard and just select user permissions
After i had the permissions.sql file and then took it to the new server and opened that in a query window and ran the script. I then restored all the databases i had on the server and everything worked fine
My personal thought on this is that you need to restore the master database, but looking through your instructions i am sure it would have failed at (sqlservr -c -m -f -T3608) with the same error message saying you SQL 2005 install has either been tampered with or not installed correctly, because before i posted this online, i did find some other articles mentioning the same thing but when i ran the command it failed
I have printed off your article and am going to keep it until i get another chance to run the instructions
Thanks for the help
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply