I would like to restore the master database from a current backup tape but I got the error “Load database must be used in single user mode if trying to restore the Master database”. I tried to change the master database option but the option ‘single user’ cannot be changed for the ‘master’ database. Then I tried to start the SQL Server in Single-User Mode but I was not able to restore the master database from a tape.
Would anybody help me on how to restore the Master database like any other database?
Many thanks
I am going through the procedure of restoring the master database as described in the Microsoft SQL 6.5 Survival Guide by SAMS. I deleted the master.dat file from c:mssqldatamaster.dat. I went into setup and choose rebuild master database. After rebuilding the master database, I went into setup again to add the "-m" server option to start server in single user mode so I can load the master database from backup. Then I went into Enterprise Manager to start the server. The server started, but when I try to double click on the server. It gave me the following
" A connection could not be established to [server name] - SQL server login failed."
I try reregistering the server, but gave me the same error. The SQL server is using standard security.
I do my backup using BACKUP database <myDB> TO DISK='D:BACKUPmyDB.bak' I do this for ALL my databases every day.
Another product writes every day all the files on the server to tape EXCEPT the online databases because these files are open by SQL server 2000.
Now I restore the files of the tape to the server This works well but I haven't got the online databases !! And because the master database isn't available, SQL server wont start ...
BUT I have the backup of the master database 'D:BACKUPMaster_data.bak'
How can I use this master backup to do a restore even when SQL server can't start ?
Or must I do the bakup of the master database in another way ?
When I attempt to restore a master database I am having some issues. I am using SQL Server 2000. I have set the start up option with -c -m to set the database in single user mode.
When I run the restore from enterprise manager I am get an popup window that is titled "SQL-DMo(odbc SQLSTATE:HY000)" with not message and then the database conection is lost. Thus the master datbase is appears to it was not resotred. As when I close enterprise manager and then restart the MSSQLSERVER service the database is in the same state.
Please advise on how i can get the Database restored. Thanks Chris
I was wondering if someone could help me. I need to re-install the SQL Server (due to a registry corruption). What I plan to do is to:
- Detach all databases (including master, pubs, and tempdb) - Copy databases to a different folder - Uninstall SQL Server - Reinstall SQL Server - Copy the databases back - Reattach all databases
Will that preserver all the security? (e.g. syslogins and sysxlogins) Will using an old "master" database on a new install pose any problems?
Any help will be GREATLY appreciated. Please email me at vchai@artinmotion.com
I have 2 database servers, 1 @ production and 1 @ DR site.
To restore the master database to DR on a regular basis, I have to rely on either the DBA or SysAdm to perform the restore. It's pretty expensive, as they have to dedicate some time to do so.
I'm exploring the possibility of getting the Data Centre staff to do this instead, as they are stationed at the DR site. Please advise the SQL permisssions required to restore master database.
I wish to restore master database which was located on "e:" on my server. the restored copy is residing on "f:" when i restore the backup and restart, the server does not start.
is there any way to avoid this and perform the restoration succesfully. or the process followed by me is wrong/incomplete.
Hello,I need to restore the Master DB in my SQL 2k server (with sp3). Theproblem is that i don't have SQL backups of this file I only havegeneral backup of the entire c: drive i made using NTBackup. So itried all I know and succesfully run the rebuildm utility. Now when Itry to restore it from the general backup (not SQL backup) using"restore database master from gen_backup" i got error: "The file ondevice 'gen_backup' is not a valid Microsoft Tape Format backup set."RESTORE DATABASE is terminating abnormally.Any ideas how I can restore it?
I have a fresh install of sqlExpress and Management Studio Express on my test server. I want to restore my master database from backup.
From the command prompt I set the Sqlservr -s SQLEXPRESS -m
Then I opened another comand prompt and ran my SQLCMD script to restore the Master Database.
here is the sql script: RESTORE DATABASE [Master] FROM DISK = N'E:COPLEYNEWSDATABASEBACKUPMaster.bak' WITH FILE = 1, MOVE N'mastlog' TO N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMaster_1.ldf', NOUNLOAD, STATS = 10 GO
I recieve the following error.
Msg 3154, Level 16, State 4, Server COPLEYNEWSSQLEXPRESS, Line 1 The backup set holds a backup of a database other than the existing 'Master' dat abase. Msg 3013, Level 16, State 1, Server COPLEYNEWSSQLEXPRESS, Line 1
How do I restore a Master Database on SQL Express?
I am having problem restoring a data i backup in master database for an application upgrade. I have this error message that ' i can only restore in single usermode for master database that i should change to single user mode'
If one is regularly taking backups of system databases, when does it become necessary to rebuild the master database. I am looking for a situation where rebuilding the master is preferred to restoring it from backup.
Login failed for user 'OFFICIALPROJECTAdministrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
I have a fresh install of sqlExpress and Management Studio Express on my test server. I want to restore my master database from backup.
From the command prompt I set the Sqlservr -s SQLEXPRESS -m
Then I opened another comand prompt and ran my SQLCMD script to restore the Master Database.
here is the sql script: RESTORE DATABASE [Master] FROM DISK = N'E:COPLEYNEWSDATABASEBACKUPMaster.bak' WITH FILE = 1, MOVE N'mastlog' TO N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAMaster_1.ldf', NOUNLOAD, STATS = 10 GO
I recieve the following error.
Msg 3154, Level 16, State 4, Server COPLEYNEWSSQLEXPRESS, Line 1 The backup set holds a backup of a database other than the existing 'Master' dat abase. Msg 3013, Level 16, State 1, Server COPLEYNEWSSQLEXPRESS, Line 1
How do I restore a Master Database on SQL Express?
Hi,We are planning to create a prod environment on our Dev server, bycreating a new named instance on DEV. Our prod server is running onsql enterprise edition 64bit and dev on SQl enterprise 32bit.Could we copy all the databases from prod server includingMaster,msdb,model onto our dev server, and bring up all the databases?Does Master store information about the version of SQl server?Thanks for your helpGG
I have never done this in a DR scenario before. What I used to do in SQL Server 2000 was apply the logins to a new installation of SQL 2000 then recover the application databases. I'm using 2005 now and I'm in a different mode where I would like to be able to recover the system (master, model, msdb) to a different server with the same SQL Server 2005 build ---- 9.0.3054. I'm following the procedures from Microsoft where they state to place the instance in single user mode first then invoke SQLCMD then perform the restore. Sounds simple enough. C:> SQLCMD > RESTORE DATABASE MASTER from DISK = 'Z:MINIDRmaster_backup_200804200315.bak'
The console is coming back with a message that the backup set holds a backup other than the existing master database. So I suppose that this cannot be done and is only intended for recovery on the same instance.
I need your advice and help on the following problem . Say for example I have a SQL 7.0 Server A with 30 databases and i am in the process of transfering these databases to another system which is SQL Server B with 7.0 again . Although the Databases can be copied by various means , can i restore Master database from the Server A to Server B so that my Server B will have all the database and login information as that of Server A.
As Master database is system database , it won't allow to be dropped and hence you cannot restore . Is there any other way to restore Master database from One SQL server 7.0 to another with the same SQL version .
Any kind of assistance will be of great help to me . Many thanks in advance .
When I try to restore master, I keep getting an error message, while it is in the process of restoring, that says the connection has been broken. I assume it means the connection for single user mode. Any ideas on how to fix this??????
Does anyone have anything that can automate the restoration of the master database in MS SQL 7? I need to do it on a daily basis on a back up server. I also need to do the 2 production databases and the msdb and model as well. Does anyone have any suggestions to keep me from reinventing the wheel?
I'm trying to nail down some procedures for disaster recovery of our SQL 6.5 databases. If I rebuild master (or do a fresh install), then create the devices based on the output of sp_helpdevice, and then create the databases based on the output of sp_help_revdatabase, do I really need to RESTORE the master database? Are there any other objects in the master database (except for maybe some custom stored procedures) that I really need from my production server?
I've just recreated the device and database structures manually, so do I really need to restore master?
Another issue: the current wisdom that I've seen is that although databases need to be grown back in the same increments, the devices do not. Therefore, you can just DISK INIT them to their current size. Does anyone think this is a bad idea?
I have a SQL 2000 server that when they installed it they changed the default settings of the locations of the data files. Master included. I now want to restore master to a standard location. However everytime I do it, I then cannot log onto SQL Server. Any suggestions!!
When I attempt to restore a master database I am having some issues. I am using SQL Server 2000. I have set the start up option with -c -m to set the database in single user mode.
When I run the restore from enterprise manager I am get an popup window that is titled "SQL-DMo(odbc SQLSTATE:HY000)" with not message and then the database conection is lost. Thus the master datbase is appears to it was not resotred. As when I close enterprise manager and then restart the MSSQLSERVER service the database is in the same state.
Please advise on how i can get the Database restored. Thanks Chris
Hi.,Can restore master database and other databases from one server toother. I cant restore master database. i stared server in single usermode, and restore master database. it says successfully restored andserver stoped. I cant up the server... :(Regardsdishan
To restore the Master DB, BOL says to start SQL Server in single user mode. I do this using: sqlserver.exe -m
The server appears to startup (Recovery complete message, etc.).
Then I take care to minimize the command window. The command window now appears on my task bar.
I start EM and navigate my way to the Restore Database menu pick.
I fill out the appropriate boxes, etc. I get a window telling me that the restore has been started.
Very shortly I get a popup window telling me the connection has been lost. When I examine my taskbar, I no longer see the Icon for the command line/window. Thus it appears to me that the server running in single user mode has gone away and this explains the reason for the window saying that the connection has been lost.
I then start SQL Server, per normal.
I run DBCC CHECKDB. This tells me there are 0 allocation errors and 0 consistency errors. Several thousand rows of data present. Thus the master DB appears to be OK.
Our SQL Server has the databases on D drive and logfiles on the E drive. I have full database backups.
I want to move the databases over to another "standby server". However, the other server does not have an E drive. It is imperative that I be able to restore the Master database. But when I do, the SQL Server will not start, because the Master database expects the logfiles on the E drive.
Is it possible to restore Master without it being able to find what it needs on the E drive? Or in order to restore Master, does it have to find those other databases/logfiles?
I am going on the "disaster recovery" scenario. Assume that the original server has crashed, and all I have are backups. Can this be done?
I have a full backup from ServerA. I then put ServerB in single user mode and try to restore the backup from ServerA. (both are identical servers but have different names)
[*- From command prompt using sqlcmd -*]
USE master GO RESTORE DATABASE master FROM DISK = 'D:BACKUPSserverA.bak' GO
Msg 3154, Level 16, State 4, Server MSSQL1, Line 1 The backup set holds a backup of a database other than the existing 'master' database.
Msg 3013, Level 16, State 1, Server MSSQL1, Line 1 RESTORE DATABASE is terminating abnormally.
Any ideas on forcing the restore through T-SQL? The restore works fine from a master backup from itself (ServerB).
Not sure where to post this. How do I restore the master database to a brand new server that has a different name than the server the master currently resides on? I am using SQL Server 2005.
I've tried starting SQL from the command line in single user mode (-m). When I try to open Management Studio I receive an error that it can't connect. Can you only restore master from a command line in SQL 2005? Or can I do it from a GUI?
I am trying to replicate our production SQL 6.5 environment on an isolated network for Y2K testing. I have installed SQL 6.5 on the test server, then ran sp_help_revdatabase on the production server to generate scripts for recreating the databases for testing. I used a zip drive to move dumps of the user databases from production to test. I now need to move over data from the master db, to recreate security and logins. But I don't think I can simply do a dump and restore, as I am not creating copies of all the production databases from our production server, just a few of them. Does anyone have a suggestion for a procedure to replicate the info I need from our production master database? Am I correct that restoring a dump of our production master database to the Y2K environment would be inappropriate?
Please advise.
Thanks,
Fred Smith fsmith@physio-control.com Physio-Control Corp.
Version: 10.00.1600 (I believe the first 2008 release)
I made a backup from the master, model and msdb system tables. Now I travelled to a different country, and I only have the backups and would like to access them. I was able to restore the master db to a new installation of mssql but, it wont start. I do know why: as I read after I quickly realised that master backups take the accounts, serverID, groups, directory structure, and lots of other things.Basically I have no chance of doing a master restore. The question here: Is there any way of getting my data out from the master backup file (thats where my stuff is)? I'm thinking of "restoring" it to a separate non-master db, table, anything. I know now that I did the most stupidest thing ever, but believe me I don't want the last half years work to just disappear.
