1、 常備份用戶的數(shù)據(jù)一、還Master 數(shù)據(jù)庫還需 常備份用戶的數(shù)據(jù)一、還Master 數(shù)據(jù)庫還需要備份系統(tǒng)數(shù)據(jù)庫 須重新生成損壞的 master 數(shù)據(jù)庫：情況：Amaster B. master SQLServer 1、重新生master master SQL Server 2005 例： start / wait e:“setup.exe /qn INSTANCENAME=mssqlserver REINSTALL=SQL_Engine REBUILDDATABASE=1SAPWD=abc123! /qn /qn SQLServer2005 /qb 2、Master 備份(master 的備

2、份時(shí)要注意：必須在單用戶(single user)模式下進(jìn)行 )a. 進(jìn)入單用戶模式的方法Method1-f -m 或者輸入-m。其中：-cSQ -m SQL 或Windows NTf SQL ，執(zhí)行 FROM開始實(shí)驗(yàn)了MASTER為了模擬MASTER數(shù)據(jù)庫壞了就刪除MASTER數(shù)據(jù)庫(要停止SQLSERVER服務(wù)才能刪除在電腦,C:“ PROGRAM SQL SERVER“MSSQL.1“MSSQL“DATE 中刪除 start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_EngineDATABASE=1 SAPWD=我這里的為默認(rèn)實(shí)例所以

3、其中的MSSQLSERVER最后的我設(shè)為 abc123!所以就用 abc123!代替若沒有返回錯(cuò)誤開始實(shí)驗(yàn)了MASTER為了模擬MASTER數(shù)據(jù)庫壞了就刪除MASTER數(shù)據(jù)庫(要停止SQLSERVER服務(wù)才能刪除在電腦,C:“ PROGRAM SQL SERVER“MSSQL.1“MSSQL“DATE 中刪除 start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_EngineDATABASE=1 SAPWD=我這里的為默認(rèn)實(shí)例所以其中的MSSQLSERVER最后的我設(shè)為 abc123!所以就用 abc123!代替若沒有返回錯(cuò)誤就可以到電腦中

4、 C:“ PROGRAM SERVER“MSSQL.1“DATEMASTER.MDF啟動(dòng)服務(wù)(MSSQL代碼Windowss and Settings/Administratorstart /wait e:/setup.exe /qn =mssqlserverREINSTALL=SQL_EngineREBUILDDATABASE=1s and Settings/Administratorcd C:/Program SQL 2008-04-1815:47:46.56ServerAuthenticationmodeis2008-04-1815:47:46.59Oct142005Copyright(

5、c)1988-SQLServer2005-9.00.1399.06elriseEditiononWindowsNT5.2(Build3790:ServicePack2008-04-1815:47:46.59Server(c)2008-04-1815:47:46.592008-04-1815:47:46.59Servers.sIDis2008-04-1815:47:46.59ServerLoggingSQLServermessagesinfileC:“ProgramSQL2008-04-1815:47:46.59ServerThisinstanceofSQLServerlastreportedu

6、singasIDof at 2008-4-18 15:46:40 (local) 2008-4-18 7:46:40 useractionis). This is an informational message only; 2008-04-1815:47:46.60ServerRegistrystartup-2008-04-1815:47:46.62dLineStartup2008-04-18 15:47:46.62 Server -m2008-04-18 15:47:46.64 SQL Server is starting at normal priority base (=7). Thi

7、s is an informational message only. No user action is required.2008-04-18 15:47:46.67 Detected 1 CPUs. This is an informational message; no user action is required.2008-04-18 15:47:46.78-2008-04-1815:47:46.62dLineStartup2008-04-18 15:47:46.62 Server -m2008-04-18 15:47:46.64 SQL Server is starting at

8、 normal priority base (=7). This is an informational message only. No user action is required.2008-04-18 15:47:46.67 Detected 1 CPUs. This is an informational message; no user action is required.2008-04-18 15:47:46.78 Using dynamic lock allocation. Initial allocation of 2500 blocks and 5000 Lock Own

9、er blocks per node. This is an informational message only. No user action is required.2008-04-18 15:47:46.84 服務(wù)器 Attempting to Distributed Coordinator(MSDTC).Thisisaninformationalmessageonly.Nouseractionis2008-04-18 15:47:47.95 服務(wù)器 Attempting to recover in-doubt distributed Distributedionoordinator(

10、MSThisisaninformationalmessageonly.Nouseractionis2008-04-18 15:47:47.96 服務(wù)器 Database Mirroring Transport is disabled in the endpo 2008-04-1815:47:47.962008-04-18 15:47:47.96 5sWarning5s SQL Server started in single-user mode. This an messageonly.Nouseractionis2008-04-1815:47:47.995sStartingupdatabas

11、e2008-04-18 15:47:48.17 5s Recovery is writing a in database master (1). This aninformationalmessageonly.Nouseractionis2008-04-1815:47:48.28 2008-04-1815:47:48.76user action is required. 2008-04-1815:47:48.78s5sSQLTraceID1wasstartedbylogin5s Server name is VM01.This is an informationalmessage only.

12、8sStartingupdatabase2008-04-18 15:47:49.20 服務(wù)器 A self-generated 2008-04-1815:47:49.23 Serverislisteningonany2008-04-18 15:47:49.24 服務(wù)器Server local connection provider is ready to accept connection on “.“pipe“SQLLocaQLSERVER .2008-04-18 15:47:49.24 Server local connection provider is ready to accept

13、connection on “.“pipe“sql“query .2008-04-1815:47:49.28 Serverislisteningon2008-04-18 15:47:49.28 服務(wù)器 Dedicated admin connection support was established for listening locally on port 1434.csfully loaded 2008-04-18 15:47:49.29 The SQL erfacelibrarycouldnotregisterthePrincipal Name (SPN) for the SQL Se

14、rver service. Error: 0 x54b. Failure to register an SPN egratedauthentication tofallback toNTLMinstead of Kerberos.Thisis an message.Further action is only required if Kerberos authentication is required by 2008-04-18 15:47:49.31 服務(wù)器SQL Server is now ready for nt connections. This is informationalme

15、ssage;nouseractionis2008-04-1815:47:49.402008-04-182008-04-18 15:47:49.31 服務(wù)器SQL Server is now ready for nt connections. This is informationalmessage;nouseractionis2008-04-1815:47:49.402008-04-1815:47:49.682008-04-18 15:47:49.93 Distributed 5sStartingupdatabase8sClearingtempdb5s Recovery of any in-d

16、oubt distributed ions ion Coordinator (MS DTC) has completed. This is an messageonly.Nouseractionis2008-04-1815:47:50.572008-04-18 15:47:50.76 user action is required. 2008-04-1815:56:22.34 s8sStartingupdatabase5s Recovery is complete. This is an informational message only. 51 SQL Trace was stopped

17、due to server shutdown. Trace ID = Thisisaninformationalmessageonly;nouseractionis2008-04-18 15:56:22.40 The SQL erface library could not deregister Service Principal Name (SPN) for the SQL Server service. Error: 0 x54b. Administrator deregisterthisSPNmanuallytoavoidntauthentication SQL 代碼: USEmaste

18、r /MSSQL/Backup/master.bakSQLmastermaster1 上)376 頁。已為數(shù)據(jù)庫 master，文件 mastlog (位于文件 1 上)處理了 6 頁。已成功地還原了 master 數(shù)據(jù)庫。正在關(guān)閉 SQL Server。SQLServer master數(shù)據(jù)庫還原成功,SSMS即可看到master附恢復(fù) master 數(shù)據(jù)庫 （代碼netstartSQLServer(MSSQLSERVER)代碼SQLServer(MSSQLSERVER) SQLServer(MSSQLSERVER) 代碼代碼 mastermaster代碼 mastermaster1 上)376 頁。已為數(shù)據(jù)庫 master，文件 mastlog (位于文件 1 上)處理了 3 頁。已成功地還原了 master 數(shù)據(jù)庫。正在關(guān)閉 SQL Serve