系統數據庫和用戶數據庫日志都丟失的情況下，數據庫無法啟動，需要先重建系統數據庫日志文件以便把sqlserver service拉起來，再重建用戶數據庫

試過但是行不通的辦法
1、-mClient單用戶模式下啟動數據庫并重建系統數據庫日志，即使用net start "SQL Server (MSSQLSERVER)" -m"Microsoft SQL Server Management Studio - Query"進入數據庫重建系統數據庫日志，出現報錯，系統數據庫日志不允許重建
System databases master, model, and tempdb cannot have their logs rebuilt.
2、設置啟動項-T3608，即在SQL Server Configuration Manager里面對應的sqlserver service服務添加啟動項參數-T3608，sqlserver service服務器還是起不來
-T3608 禁止 SQL Server 自動啟動和恢復除 master 數據庫之外的任何數據庫。
3、使用安裝包repair現有sqlserver實例，行不通，因為repair其實是需要sqlserver實例有startup handle，而系統數據庫日志都丟失的情況下，sqlserver實例壓根無法啟動，也就么有startup handle的概念，所以repair一個沒有系統數據庫日志的sqlserver實例會報錯：Could not find the Database Engine startup handle
4、完全卸載，再重新安裝，這個時候是可以把數據庫拉起來的，不過master庫的東西和msdb庫的東西都丟失了，不過可以重建msdb庫，重建msdb的前提是master和model庫必須正常，也就是一個數據庫能用，必須保證master和model庫正常，因為一個sqlserver 實例能否使用必須有tempdb，而tempdb依賴model，model依賴master



Sqlserver系統數據庫和用戶數據庫日志文件丟失的恢復方法

1、系統數據庫有備份的話，可以把系統數據庫以重命名的方式當成用戶數據庫恢復到其他實例，但是必須保證恢復后的數據文件和日志文件的名稱和之前一樣，再把恢復后的數據文件和日志文件拷貝回出問題的實例。比如A實例的系統數據庫master的日志文件丟失了，可以把A實例的master庫恢復到B實例master_test庫,但是必須保證B的master_test庫的數據文件和日志文件名稱和A庫的master庫的數據文件和日志文件名稱一致，再把B的master_test庫的數據文件和日志文件名稱拷貝回A實例master庫對應的目錄，這樣A實例就可以啟動了

2、如果系統數據庫沒有備份，則在其他實例上建立一個數據庫，數據文件和日志文件名稱和丟失的系統數據庫一樣，再把新建的數據庫offline，把這個新建數據庫的數據文件和日志文件改名或拷貝到其他目錄，再把丟失日志的系統數據庫數據文件拷貝到這個新建數據庫的對應的目錄，再執行rebuild log，這樣這個數據文件就自動生成了日志文件，再把這個系統數據庫數據文件和日志文件拷貝回丟失系統數據庫日志文件的目錄，這時這個曾經丟失了系統數據庫日志文件的sqlserver實例就可以啟動了，參見如下示例

假如A實例的master和model和msdb的日志文件都丟了
2.1、在B實例建立和master、model、msdb同名數據庫文件FILENAME的用戶數據庫master_test、model_test、msdb_test

1 2 3 4 5 6 7 8 9 10 11 12

CREATE?DATABASE?[master_test]? ON?PRIMARY(NAME?=?N'master',?FILENAME?=?N'E:\master.mdf') LOG?ON?(NAME?=?N'mastlog',?FILENAME?=?N'E:\mastlog.ldf') GO CREATE?DATABASE?[model_test]? ON?PRIMARY(NAME?=?N'modeldev',?FILENAME?=?N'E:\model.mdf') LOG?ON?(NAME?=?N'modellog',?FILENAME?=?N'E:\modellog.ldf') GO CREATE?DATABASE?[msdb_test]? ON?PRIMARY(NAME?=?N'MSDBData',?FILENAME?=?N'E:\msdbdata.mdf') LOG?ON?(NAME?=?N'MSDBLog',?FILENAME?=?N'E:\MSDBLog.ldf') GO

2.2、在B實例對這三個用戶數據庫master_test、model_test、msdb_test進行offline脫機操作

1 2 3

alter?database?[master_test]?set?offline alter?database?[model_test]?set?offline alter?database?[msdb_test]?set?offline

2.3、再在B實例把master_test、model_test、msdb_test數據文件和日志文件拷貝到其他目錄或改名，再把A實例的master、model、msdb數據文件拷貝到B實例master_test、model_test、msdb_test數據庫對應目錄，再執行REBUILD LOG，這樣A實例的master、model、msdb數據文件其實就有日志文件，把這些數據文件和日志拷貝會A實例對應目錄，A實例就可以啟動了

1 2 3

ALTER?DATABASE?[master_test]?REBUILD?LOG?ON?(NAME=master_log,?FILENAME='E:\mastlog.ldf'); ALTER?DATABASE?[model_test]?REBUILD?LOG?ON?(NAME=modellog,?FILENAME='E:\modellog.ldf'); ALTER?DATABASE?[msdb_test]?REBUILD?LOG?ON?(NAME=MSDBLog,?FILENAME='E:\msdblog.ldf');

會有如下警告，說明生成了新的日志文件
Warning: The log for database 'master_test' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Warning: The log for database 'model_test' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Warning: The log for database 'msdb_test' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

3、再回到A實例，在A實例執行如下語句，把生成的語句再執行一遍就可以rebuild 用戶數據庫的日志文件，并設置數據庫的訪問限制為多用戶

1 2 3 4

select?'ALTER?DATABASE?['+db_name(database_id)+']?REBUILD?LOG?ON?(NAME='+name+',? FILENAME='''+physical_name+''');'?from?master.sys.master_files?where?type_desc='LOG'?and?database_id>4 select?'ALTER?DATABASE?['+db_name(database_id)+']?SET?MULTI_USER?WITH?NO_WAIT'? from?master.sys.master_files?where?type_desc='LOG'?and?database_id>4

備注：目前官方文檔沒有ALTER DATABASE REBUILD LOG的操作，sqlserver不推薦這種操作
?

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的Sqlserver系统数据库和用户数据库日志文件全部丢失的恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。