SQL 2012 :: When To Restore And Rebuild Master Database
May 11, 2014
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.
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 need to rebuild a corrupted/deleted master database in SQL Server 2005 SP2.
When I run setup.exe from the install media as directed by SQL Books Online, I get to the point where it says the instance is a later version and setup cannot continue. Rerunning the SP2 installer, the instances cannot be selected for upgrade (and it ignores the command line options for the original installer).
How to proceed?
Also, it would seem simpler to have a detach, copy and attach the system databases process, rather than run the installer. (I realize you can stop the instance, copy files, restart the instance; but that's not "high availability".)
Hi, I'm testing my recovery on a sql server 2005 database. The server has been restored, which includes the OS and sql server 2005 installation (binaries, full tape restore). The tape restore didn't put back the master.mdf and other .mdf files (can't be backed up when open), so I need to rebuild the master and do a database recovery on it and the other supporting databases (yes, I have database backups). I have installed the sql 2005 setup CDs in a folder and run the following command to rebuild the master, but nothing happens, it pauses for a couple of minutes before it returns to the prompt, but it does not put and new master.mdf files in the folder and I can't start the service in single user mode. "start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword" Anybody have any procedures for a full sql server 2005 recovery or suggestions on this, please post.
I'm trying to rebuild my master database for sql server 2000. The process of rebuilding stared fine. But it is almost 4 hours since it got started. Performing it on a test system. Got doubtful and started the same on another test system. Issue is same and it is almost 2 hours. The Db size is less than 100 MB in both cases. IS IT NORMAL? I've tried the same for SQL SERVER 2005 and it got finished in couple of minutes. Please advise.
Complete: SetPackageInstallStateAction at: 2007/9/23 7:33:43, returned true Running: DeterminePackageTransformsAction at: 2007/9/23 7:33:43 Complete: DeterminePackageTransformsAction at: 2007/9/23 7:33:43, returned true Running: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43 Complete: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43, returned true Running: OpenPipeAction at: 2007/9/23 7:33:43 Complete: OpenPipeAction at: 2007/9/23 7:33:43, returned false Error: Action "OpenPipeAction" failed during execution. Running: CreatePipeAction at: 2007/9/23 7:33:43 Complete: CreatePipeAction at: 2007/9/23 7:33:43, returned true Running: RunRemoteSetupAction at: 2007/9/23 7:33:43 <Func Name='CProcessCtrl::GetInstallPath'> <EndFunc Name='CProcessCtrl::GetInstallPath' Return='0' GetLastError='0'> Error: 0x80070050 TaskScheduler::NewWorkItem for SQL Server Remote Setup Error: 0x80070005 TaskSchedulerWorkItem failed to save the task [SQL Server Remote Setup ] Complete: RunRemoteSetupAction at: 2007/9/23 7:33:43, returned false Error: Action "RunRemoteSetupAction" failed during execution. Error information reported during run: Attempting to determine log files for remote install. Connection to remote computer's scheduler service. Creating new workitem. Deleting existing work item and trying again... Starting remote setup onSQL1N2 Error: 80070005 Access is denied. Running: PopulateMutatorDbAction at: 2007/9/23 7:33:43 Complete: PopulateMutatorDbAction at: 2007/9/23 7:33:43, returned true Running: GenerateRequestsAction at: 2007/9/23 7:33:43 SQL_Engine = 3 SQL_Data_Files = -1 SQL_Replication = -1 SQL_FullText = -1 SQL_SharedTools = -1 SQL_BC_DEP = -1 Analysis_Server = -1 AnalysisDataFiles = -1 AnalysisSharedTools = -1 RS_Server = -1 RS_Web_Interface = -1 RS_SharedTools = -1 Notification_Services = -1 NS_Engine = -1 NS_Client = -1 SQL_DTS = -1 Client_Components = -1 Connectivity = -1 SQL_Tools90 = -1 SQL_WarehouseDevWorkbench = -1 SDK = -1 SQLXML = -1 Tools_Legacy = -1 TOOLS_BC_DEP = -1 SQL_SSMSEE = -1 SQL_Documentation = -1 SQL_BooksOnline = -1 SQL_DatabaseSamples = -1 SQL_AdventureWorksSamples = -1 SQL_AdventureWorksDWSamples = -1 SQL_AdventureWorksASSamples = -1 SQL_Samples = -1 Complete: GenerateRequestsAction at: 2007/9/23 7:33:44, returned true Running: CreateProgressWindowAction at: 2007/9/23 7:33:44 Complete: CreateProgressWindowAction at: 2007/9/23 7:33:44, returned false Error: Action "CreateProgressWindowAction" failed during execution. Running: ScheduleActionAction at: 2007/9/23 7:33:44 Complete: ScheduleActionAction at: 2007/9/23 7:33:45, returned true Skipped: InstallASAction.11 Waiting for actions from remote setup(s) Breaking wait state and aborting package due to cancel code received: 1602 Remote setup(s) are ready Notify package action is determined: 1602 Error Code: 0x800700e9 (233) Windows Error Text: No process is on the other end of the pipe.
Source File Name: remotemessageliboverlappedpipelistener.cpp Compiler Timestamp: Sat Oct 7 09:43:54 2006 Function Name: sqls:verlappedPipeListener::writePipe Source Line Number: 294
Notification failed to send. ---- Context ----------------------------------------------- sqls::HostSetupPackageInstallerSynch::installAction
Removing machine from list of targets to sync. Skipped: Action "InstallASAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "as", will not be installed. Skipped: InstallASAction.18 Skipped: Action "InstallASAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "as", will not be installed. Skipped: InstallASAction.22 Skipped: Action "InstallASAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "as", will not be installed. Skipped: InstallASAction.9 Skipped: Action "InstallASAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "as", will not be installed. Skipped: InstallDTSAction Skipped: Action "InstallDTSAction" was not run. Information reported during analysis: All installs have been cancelled, so package: "dts", will not be installed. Skipped: InstallDTSAction.11 Skipped: Action "InstallDTSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.12 Skipped: Action "InstallDTSAction.12" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlncli", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.18 Skipped: Action "InstallDTSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.22 Skipped: Action "InstallDTSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.9 Skipped: Action "InstallDTSAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "dts", will not be installed. Skipped: InstallNSAction Skipped: Action "InstallNSAction" was not run. Information reported during analysis: All installs have been cancelled, so package: "ns", will not be installed. Skipped: InstallNSAction.11 Skipped: Action "InstallNSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "ns", will not be installed. Skipped: InstallNSAction.12 Skipped: Action "InstallNSAction.12" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlncli", referred by package: "ns", will not be installed. Skipped: InstallNSAction.18 Skipped: Action "InstallNSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "ns", will not be installed. Skipped: InstallNSAction.22 Skipped: Action "InstallNSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "ns", will not be installed. Skipped: InstallNSAction.9 Skipped: Action "InstallNSAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "ns", will not be installed. Skipped: InstallRSAction.11 Skipped: Action "InstallRSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "rs", will not be installed. Skipped: InstallRSAction.18 Skipped: Action "InstallRSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "rs", will not be installed. Skipped: InstallRSAction.22 Skipped: Action "InstallRSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "rs", will not be installed. Skipped: InstallSqlAction Clustered feature detected: SQL_Engine Clustered feature detected: SQL_FullText Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.3042.0 Windows Error Text: User cancelled installation.
Source File Name: sqlchainingsqlchainingactions.cpp Compiler Timestamp: Thu Nov 16 20:32:00 2006 Function Name: sqls::ReportChainingResults:erform Source Line Number: 3667
---- Context ----------------------------------------------- sqls::RunRemoteSetupAction::waitForRemoteSetupComplete king package: "patchRS2000" as failed due to cancel code received from cancel source: 1602 sqls Delay load of action "UploadDrWatsonLogAction" returned nothing. No action will occur as a result. Message pump returning: 1602
I have this situation: -Two nodes cluster with two instances sql cluster 2012 and I need to move all on the new cluster 2012 with finally the same virtually name and instances.
It's possible to restore master order to preserve all security?
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'
I am performing a Rebiuld of the Master database using the REBUILDM utility on a SQL 2000 SP3 database which is clustered. The utility starts off correclty, copying the correct MDF and LDF files to the hard drive, and the configuration bar goes across the screen 4 times, before giving the following error "Rebuild master failed with error -1".
I can't find anything on microsoft. I followed KB-298568 to perform this process, but the SQL Service will now not start as it cannot find the master, as it did not get rebuilt properly.
I have included screen shots of the error, in the zipped up word doc.
I've been in support for over ten years, but have had minimal exposure to SQL. I have a client who is running SQL 2005 (as part of SBS Premium), who experienced a server crash due to the database "administrator" filling up the C: drive with manual SQL backups.
Anyway, if I go into SQL Management Studio and try to pull up properties of any database I get a "cannot show requested dialog" error. The only other issue that I've found is that a maintenance plan doesn't quite work correctly -- It will backup their database, but not delete old copies off the hard drive. Otherwise, SQL seems to be running okay.
I have come to the conclusion that I probably need to rebuild the master database. I have tried restoring the master database from a version prior to the crash, but I still have the same issue. I believe the rebuild should be fairly easy, but just don't have a full understanding of what it will affect. They have one custom application that uses a single SQL database and very little security customization (the custom application uses one Windows AD account to access the database).
Basically, I'm wondering:
1. Can someone summarize what is contained in the master database 2. I assume that I'm looking at downtime on their custom application/database - Would this be correct? 3. Will this affect their database at all? 4. How long does the database rebuild process take? 5. I understand that in SQL 2005, the rebuild process it part of the setup on the CD. If the problem is with SQL application files themselves, will this process restore any corrupt program files?
I'm using SQL SERVER 2000 and in the process of establishing recoveryguide.Tried to rebuild master using rebuildm.exe but the "Configuring Server"never ending. Then I tried to copy original databases from SQLServerInstallation CD to hard disk as the "Browse for folder", but gave thesame problem. Need advice what need to be done.
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)
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?
Looks like I'm screwed. Next problem - can you rebuild the master.mdf without the mastlog.ldf and how? Also, on an active/active SQL server setup, how would you re-install Node 1?
I tried to test Rebuildm.exe on my local server 1. Stoped Sql 2. Renamed master.mdf to master.mdf_old 3. Tried to start Sql (Does start of couse) 4. Run Rebuildm.exe ... and "Gonfigurating Sql server" message goes forever (I was waiting 1 1/2 hrs ... progress bar was running )
In SQL Server 2005 Express, how do I rebuild the master database?
I am receiving the following error messages.
The log scan number (888:136:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
AND
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
I tried going through Add and Remove Programs. It did not recognize anything wrong. It did nothing.
I tried 'setup' from the install media. It said to go to 'Add and Remove Programs.'
In SQL Server 2005 Express, how do I rebuild the master database?
How I can rebuild master db to change the Code Page of the current SQL7 , to convert my SQL7 from code page 850 to 1252 ? I have huge data in the old code page dbs , Does rebuild the master db mean reinsert the data again ?
Is it possible to put master database to single mode, if yes then whats the procedure. The reason behind the scenario is to restore master database from backup for which its strictly recommended to put the database in single mode. But I m not able to do so.
I am working on a Login migration issue. I have the Master database from the original database, that I have attached as a User Database, call it OriginalMaster. I also renamed the mdf and ldf files, before attaching, just in case.
I want to select the logins from the OriginalMaster, and using sp_help_revlogin script all the Logins out. I know there were many logins in that database. But what I am seeing, are all the Logins from the Current Master database. Which are only 7 Logins.
How can I select the logins, or script out the Logins from the OriginalMaster database?
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