SQL SERVER 2005 Master備份與還原
一、備份Master 及相關的系統數據庫
由于系統數據庫對Sql Server來說尤其重要，為了確保SQL SERVER系統的正常運行，除了日常備份用戶的數據庫之外，我們還需要備份系統數據庫，如對Master，Model，Msdb（TempDB不需備份）進行完整備份
二、還原Master數據庫
如果系統配置丟失或Master出現問題，可以進入單用戶模式進行還原；如果出現下列情況，必須重新生成損壞的 master 數據庫：
A. master 數據庫的當前備份不可用。
B. 存在 master 數據庫備份，但由于 Microsoft SQL Server 實例無法啟動，因此無法還原該備份。
1、重新生成 master 數據庫：
注意：
在 SQL Server 2005 中已廢止 Rebuildm.exe 程序。若要重新生成 master 數據庫，請使用 setup.exe。
1、 Start /wait setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>
例：start /wait e:/setup.exe /qn INSTANCENAME=mssqlserver REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abc123@!@
注：INSTANCENAME：指定實例名，默認實例則用mssqlserver表示
REINSTALL：指定引擎
SAPWD：強密碼
Setup.exe：指定光盤1中的根目錄下的文件
/qn 開關用于取消所有安裝程序對話框和錯誤消息。如果指定 /qn 開關，則所有安裝程序消息（包括錯誤消息）都將寫入安裝程序日志文件。有關日志文件的詳細信息，請參閱如何查看 SQL Server 2005 安裝日志文件。
2、 還原Master備份
先進入單用戶模式，系統默認沒有設置PATH，先進入CMD，進入“C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn”，執行SQLSERVR.EXE –M
打開SSMS工具，先斷開連接，再新建查詢，執行以下命名還原
USE master
GO
RESTORE DATABASE master
FROM disk='c:/master.bak'
GO
開始實驗了~~~
MASTER重新生成
為了模擬MASTER數據庫壞了,我們就刪除MASTER數據庫(要停止SQL SERVER服務才能刪除)在我的電腦,C:/ PROGRAM FILES/MICROSOFT SQL SERVER/MSSQL.1/MSSQL/DATE 中刪除MASTER.MDF
重新生成MASTER
1.首先在CMD中輸入
start /wait setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>
其中的 SETUP.EXE為啟動光盤的路徑,我這里的是 E:/SETUP.EXE /QN
我這里的為默認實例所以其中的<INSTANCENAME>用 MSSQLSERVER代替
最后的<NewStrongPassword>為密碼我設密碼為abc123@!@,所以就用abc123@!@代替<NewStrongPassword>
若沒有返回錯誤,我們就可以到我的電腦中C:/ PROGRAM FILES/MICROSOFT SQL SERVER/MSSQL.1/DATE上面又有MASTER.MDF了
單用戶模式
啟動SSMS工具,新建查詢
輸入命令
啟動服務(MSSQL SERVER)
打開SSMS
連接到數據庫后就可以發現數據庫中的數據又回來了
(C) 版權所有 1985-2003 Microsoft Corp.
C:/Documents and Settings/Administrator>start /wait e:/setup.exe /qn INSTANCENAME=mssqlserver REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=abc123@!@
C:/Documents and Settings/Administrator>cd C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn
C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn>sqlservr.exe -m
2008-04-18 15:47:46.56 Server Authentication mode is MIXED.
2008-04-18 15:47:46.59 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2008-04-18 15:47:46.56 Server Authentication mode is MIXED.
2008-04-18 15:47:46.59 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2008-04-18 15:47:46.59 Server (c) 2005 Microsoft Corporation.
2008-04-18 15:47:46.59 Server All rights reserved.
2008-04-18 15:47:46.59 Server Server process ID is 3984.
2008-04-18 15:47:46.59 Server Logging SQL Server messages in file 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/ERRORLOG'.
2008-04-18 15:47:46.59 Server This instance of SQL Server last reported using a process ID of 764 at 2008-4-18 15:46:40 (local) 2008-4-18 7:46:40 (UTC). This is an informational message only; no user action is required.
2008-04-18 15:47:46.60 Server Registry startup parameters:
2008-04-18 15:47:46.60 Server -d C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/master.mdf
2008-04-18 15:47:46.60 Server -e C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/ERRORLOG
2008-04-18 15:47:46.60 Server -l C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/mastlog.ldf
2008-04-18 15:47:46.62 Server Command Line Startup Parameters:
2008-04-18 15:47:46.62 Server -m
2008-04-18 15:47:46.64 服務器 SQL Server is starting at 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 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2008-04-18 15:47:46.84 服務器 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-04-18 15:47:47.95 服務器 Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction oordinator (MS DTC).
This is an informational message only. No user action is required.
2008-04-18 15:47:47.96 服務器 Database Mirroring Transport is disabled in the endpoint configuration.
2008-04-18 15:47:47.96 spid5s Warning ******************
2008-04-18 15:47:47.96 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2008-04-18 15:47:47.99 spid5s Starting up database 'master'.
2008-04-18 15:47:48.17 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-04-18 15:47:46.59 Server All rights reserved.
2008-04-18 15:47:46.59 Server Server process ID is 3984.
2008-04-18 15:47:46.59 Server Logging SQL Server messages in file 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/ERRORLOG'.
2008-04-18 15:47:46.59 Server This instance of SQL Server last reported using a process ID of 764 at 2008-4-18 15:46:40 (local) 2008-4-18 7:46:40 (UTC). This is an informational message only; no user action is required.
2008-04-18 15:47:46.60 Server Registry startup parameters:
2008-04-18 15:47:46.60 Server -d C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/master.mdf
2008-04-18 15:47:46.60 Server -e C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/LOG/ERRORLOG
2008-04-18 15:47:46.60 Server -l C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/mastlog.ldf
2008-04-18 15:47:46.62 Server Command Line Startup Parameters:
2008-04-18 15:47:46.62 Server -m
2008-04-18 15:47:46.64 服務器 SQL Server is starting at 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 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2008-04-18 15:47:46.84 服務器 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2008-04-18 15:47:47.95 服務器 Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction oordinator (MS DTC).
This is an informational message only. No user action is required.
2008-04-18 15:47:47.96 服務器 Database Mirroring Transport is disabled in the endpoint configuration.
2008-04-18 15:47:47.96 spid5s Warning ******************
2008-04-18 15:47:47.96 spid5s SQL Server started in single-user mode. This an informational message only. No user action is required.
2008-04-18 15:47:47.99 spid5s Starting up database 'master'.
2008-04-18 15:47:48.17 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2008-04-18 15:47:48.28 spid5s SQL Trace ID 1 was started by login "sa".
2008-04-18 15:47:48.32 spid5s Starting up database ssqlsystemresource'.
2008-04-18 15:47:48.76 spid5s Server name is 'VM01'. This is an informational message only. No user action is required.
2008-04-18 15:47:48.78 spid8s Starting up database 'model'.
2008-04-18 15:47:49.20 服務器 A self-generated certificate was successfully loaded for encryption.
2008-04-18 15:47:49.23 服務器 Server is listening on [ 'any' <ipv4> 1433].
2008-04-18 15:47:49.24 服務器 Server local connection provider is ready to accept connection on [ //./pipe/SQLLocal/MSSQLSERVER ].
2008-04-18 15:47:49.24 服務器 Server local connection provider is ready to accept connection on [ //./pipe/sql/query ].
2008-04-18 15:47:49.28 服務器 Server is listening on [ 127.0.0.1 <ipv4>1434].
2008-04-18 15:47:49.28 服務器 Dedicated admin connection support was established for listening locally on port 1434.
2008-04-18 15:47:49.29 服務器 The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2008-04-18 15:47:49.31 服務器 SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-04-18 15:47:49.40 spid5s Starting up database 'msdb'.
2008-04-18 15:47:49.68 spid8s Clearing tempdb database.
2008-04-18 15:47:49.93 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2008-04-18 15:47:50.57 spid8s Starting up database 'tempdb'.
2008-04-18 15:47:50.76 spid5s Recovery is complete. This is an informational message only. No user action is required.
2008-04-18 15:56:22.34 spid51 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2008-04-18 15:56:22.40 服務器 The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
2008-04-18 15:47:48.32 spid5s Starting up database ssqlsystemresource'.
2008-04-18 15:47:48.76 spid5s Server name is 'VM01'. This is an informational message only. No user action is required.
2008-04-18 15:47:48.78 spid8s Starting up database 'model'.
2008-04-18 15:47:49.20 服務器 A self-generated certificate was successfully loaded for encryption.
2008-04-18 15:47:49.23 服務器 Server is listening on [ 'any' <ipv4> 1433].
2008-04-18 15:47:49.24 服務器 Server local connection provider is ready to accept connection on [ //./pipe/SQLLocal/MSSQLSERVER ].
2008-04-18 15:47:49.24 服務器 Server local connection provider is ready to accept connection on [ //./pipe/sql/query ].
2008-04-18 15:47:49.28 服務器 Server is listening on [ 127.0.0.1 <ipv4>1434].
2008-04-18 15:47:49.28 服務器 Dedicated admin connection support was established for listening locally on port 1434.
2008-04-18 15:47:49.29 服務器 The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2008-04-18 15:47:49.31 服務器 SQL Server is now ready for client connections. This is an informational message; no user action is required.
2008-04-18 15:47:49.40 spid5s Starting up database 'msdb'.
2008-04-18 15:47:49.68 spid8s Clearing tempdb database.
2008-04-18 15:47:49.93 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2008-04-18 15:47:50.57 spid8s Starting up database 'tempdb'.
2008-04-18 15:47:50.76 spid5s Recovery is complete. This is an informational message only. No user action is required.
2008-04-18 15:56:22.34 spid51 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2008-04-18 15:56:22.40 服務器 The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Binn>
打開SSMS工具，先斷開連接，再新建查詢，執行以下命名還原
GO
RESTORE DATABASE master
FROM disk='C:/Program Files/Microsoft SQL Server/MSSQL.1 /MSSQL/Backup/master.bak'
GO
GO
數據庫中的顯示的消息應為：
已為數據庫 'master'，文件 'master' (位于文件 1 上)處理了 376 頁。
已為數據庫 'master'，文件 'mastlog' (位于文件 1 上)處理了 6 頁。
已成功地還原了 master 數據庫。正在關閉 SQL Server。
SQL Server 正在終止此進程。
已為數據庫 'master'，文件 'mastlog' (位于文件 1 上)處理了 6 頁。
已成功地還原了 master 數據庫。正在關閉 SQL Server。
SQL Server 正在終止此進程。
則表示master數據庫還原成功,啟動服務后進入SSMS即可看到master數據庫了 ^ ^
作者：
RDIF
出處：
http://www.rzrgm.cn/huyong/
Email：
406590790@qq.com
QQ：
406590790
微信：
13005007127(同手機號)
框架官網：
http://www.guosisoft.com/
http://www.rdiframework.net/
框架其他博客：
http://blog.csdn.net/chinahuyong
http://www.rzrgm.cn/huyong
國思RDIF開發框架
，
給用戶和開發者最佳的.Net框架平臺方案，為企業快速構建跨平臺、企業級的應用提供強大支持。
關于作者：系統架構師、信息系統項目管理師、DBA。專注于微軟平臺項目架構、管理和企業解決方案，多年項目開發與管理經驗，曾多次組織并開發多個大型項目，在面向對象、面向服務以及數據庫領域有一定的造詣。現主要從事基于
RDIF
框架的技術開發、咨詢工作，主要服務于金融、醫療衛生、鐵路、電信、物流、物聯網、制造、零售等行業。
如有問題或建議，請多多賜教！
本文版權歸作者和CNBLOGS博客共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，如有問題，可以通過微信、郵箱、QQ等聯系我，非常感謝。
浙公網安備 33010602011771號