SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdataba...

转:http://blog.sqlauthority.com/2007/07/21/sql-server-fix-error-msg-1813-level-16-state-2-line-1-could-not-open-new-database-yourdatabasename-create-database-is-aborted/

SQL SERVER – Fix : Error Msg 1813, Level 16, State 2, Line 1 Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

Fix : Error Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

This errors happens when corrupt database log are attempted to attach to new server. Solution of this error is little long and it involves restart of the server. I recommend following all the steps below in order without skipping any of them.

Fix/Solution/Workaround:

SQL Server logs are corrupted and they need to be rebuilt to make the database operational.
Follow all the steps in order. Replace the yourdatabasename name with real name of your database.
1. Create a new database with same name which you are trying to recover or restore. (In our error message it is yourdatabasename). Make sure the name of the MDF file (primary data file) and LDF files (Log files) same as previous database data and log file.

2. Stop SQL Server. Move original MDF file from older server (or location) to new server (or location) by replacing just created MDF file. Delete the LDF file of new server just created.

3. Start SQL Server. Database will be marked as suspect, which is expected.

4. Make sure system tables of Master database allows to update the values.
USE MASTER
GO
sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

5. Change database mode to emergency mode.
–Following statement will return the current status of the database
SELECT *
FROM sysdatabases
WHERE name = 'yourdatabasename'

—-Following statement will update only one row in database
BEGIN
UPDATE
sysdatabases
SET status = 32768
WHERE name = 'yourdatabasename'
COMMIT TRAN

6. Restart SQL Server (This is must, if it is not done SQL Server will through an error)

7. Execute this DBCC command in query window of Management Studio, this will create new log file. Keep the name of this file same as LDF file just deleted from new server :
DBCC TRACEON (3604)
DBCC REBUILD_LOG(yourdatabasename,'c:\yourdatabasename_log.ldf')
GO

DBCC accepts two parameters : first parameter is database name and second parameter is physical path of the log file. Make sure the path is physical, if you put logical file name it will return an error.

8. Reset the database status using following command.
sp_RESETSTATUS yourdatabasename
GO

9. Turn off the update to system tables of Master database running following script.
USE MASTER
GO
sp_CONFIGURE 'allow updates',0
RECONFIGURE WITH OVERRIDE
GO

This should be resolve the problem mentioned above. I always check consistence of the database as well as I reset the status of the database to original status.

10. Reset the database status to previous status
–Following statement will update only one row in database
BEGIN
UPDATE
sysdatabases
SET status = (value retrieved IN first query OF STEP 5)
WHERE name = 'yourdatabasename‘
COMMIT TRAN
GO'

Note : If during steps 8, 9 , 10 if there is error if database is in use.
Set the database to status single user.
sp_DBOPTION 'yourdatabasename', 'single user','true'
Once the steps 8,9,10 are completed if database is not already in multi user mode run this script.
sp_DBOPTION 'yourdatabasename', 'single user','false'
If there is any issue while doing above process to fix the error let me know. Make sure that you have done all the steps in order and restarted SQL Server where it is mentioned.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL (many topics)

转载于:https://www.cnblogs.com/miaomiaoga/archive/2011/04/06/2007109.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值