Sqlserver系统数据库和⽤户数据库⽇志⽂件全部丢失的恢复
数据库恢复系统数据库和⽤户数据库⽇志都丢失的情况下，数据库⽆法启动，需要先重建系统数据库⽇志⽂件以便把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
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脱机操作
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实例就可以启动了鹅蛋的好处有哪些
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. Wa
rning: 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 ⽤户数据库的⽇志⽂件，并设置数据库的访问限制为多⽤户
select'ALTER DATABASE ['+db_name(database_id)+'] REBUILD LOG ON (NAME='+name+', FILENAME='''+physical_name+''');'from master.sys.master_f iles 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 dat abase_id>4
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论