MS SQL server在附加多個.MDF文件時重新建立日志
USE [master]
GO
/****** Object: Database [MyDB1] Date: 08/11/2009 11:54:20 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB1')
DROP DATABASE [MyDB1]
GO
CREATE DATABASE MyDB1
ON
( NAME = MyDB1_dat1,FILENAME = 'C:\Data\MyDB1.mdf'),
( NAME = MyDB1_dat2,FILENAME = 'C:\Data\MyDB2.mdf'),
( NAME = MyDB1_dat3,FILENAME = 'C:\Data\MyDB3.mdf'),
( NAME = MyDB1_dat4,FILENAME = 'C:\Data\MyDB4.mdf'),
( NAME = MyDB1_dat5,FILENAME = 'C:\Data\MyDB5.mdf'),
( NAME = MyDB1_dat6,FILENAME = 'C:\Data\MyDB6.mdf'),
( NAME = MyDB1_dat7,FILENAME = 'C:\Data\MyDB7.mdf'),
( NAME = MyDB1_dat8,FILENAME = 'C:\Data\MyDB8.mdf'),
( NAME = MyDB1_dat9,FILENAME = 'C:\Data\MyDB9.mdf'),
( NAME = MyDB1_dat10,FILENAME = 'C:\Data\MyDB10.mdf'),
( NAME = MyDB1_dat11,FILENAME = 'C:\Data\MyDB11.mdf'),
( NAME = MyDB1_dat12,FILENAME = 'C:\Data\MyDB12.mdf'),
( NAME = MyDB1_dat13,FILENAME = 'C:\Data\MyDB13.mdf'),
( NAME = MyDB1_dat14,FILENAME = 'C:\Data\MyDB14.mdf'),
( NAME = MyDB1_dat15,FILENAME = 'C:\Data\MyDB15.mdf'),
( NAME = MyDB1_dat16,FILENAME = 'C:\Data\MyDB16.mdf'),
( NAME = MyDB1_dat17,FILENAME = 'C:\Data\MyDB17.mdf'),
( NAME = MyDB1_dat18,FILENAME = 'C:\Data\MyDB18.mdf')
LOG ON
( NAME = MyDB1_log1,FILENAME = 'C:\Data\MyDB1.ldf'),
( NAME = MyDB1_log2,FILENAME = 'C:\Data\MyDB2.ldf'),
( NAME = MyDB1_log3,FILENAME = 'C:\Data\MyDB3.ldf'),
( NAME = MyDB1_log4,FILENAME = 'C:\Data\MyDB4.ldf'),
( NAME = MyDB1_log5,FILENAME = 'C:\Data\MyDB5.ldf'),
( NAME = MyDB1_log6,FILENAME = 'C:\Data\MyDB6.ldf'),
( NAME = MyDB1_log7,FILENAME = 'C:\Data\MyDB7.ldf'),
( NAME = MyDB1_log8,FILENAME = 'C:\Data\MyDB8.ldf'),
( NAME = MyDB1_log9,FILENAME = 'C:\Data\MyDB9.ldf'),
( NAME = MyDB1_log10,FILENAME = 'C:\Data\MyDB10.ldf'),
( NAME = MyDB1_log11,FILENAME = 'C:\Data\MyDB11.ldf'),
( NAME = MyDB1_log12,FILENAME = 'C:\Data\MyDB12.ldf'),
( NAME = MyDB1_log13,FILENAME = 'C:\Data\MyDB13.ldf'),
( NAME = MyDB1_log14,FILENAME = 'C:\Data\MyDB14.ldf'),
( NAME = MyDB1_log15,FILENAME = 'C:\Data\MyDB15.ldf'),
( NAME = MyDB1_log16,FILENAME = 'C:\Data\MyDB16.ldf'),
( NAME = MyDB1_log17,FILENAME = 'C:\Data\MyDB17.ldf')
GO
讓我們分離這個數據庫并刪除所有的.MDF 文件。
use master
go
sp_detach_db MyDB1
go
exec master..xp_cmdshell 'Del c:\data\mydb1.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb2.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb3.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb4.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb5.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb6.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb7.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb8.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb9.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb10.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb11.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb12.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb13.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb14.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb15.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb16.ldf'
go
exec master..xp_cmdshell 'Del c:\data\mydb17.ldf'
go
注意:我使用xp_cmdshell來刪除.ldf文件。如果你沒有激活xp_cmdshell,那么你會得到下面的錯誤。
錯誤:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell'
because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more
information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
你可以使用下面的事務SQL語句來激活xp_cmdshell 。
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go
或者,你可以在MS-DOS命令提示符中使用Windows Explorer的“Del”命令來刪除.ldf文件。
現在,讓我們使用sp_attach_db試著只附加.MDF文件。執行下面的命令。
sp_attach_db 'MyDB1','C:\Data\MyDB1.mdf',
'C:\Data\MyDB2.mdf','C:\Data\MyDB3.mdf',
'C:\Data\MyDB4.mdf','C:\Data\MyDB5.mdf',
'C:\Data\MyDB6.mdf','C:\Data\MyDB7.mdf',
'C:\Data\MyDB8.mdf','C:\Data\MyDB9.mdf',
'C:\Data\MyDB10.mdf','C:\Data\MyDB11.mdf',
'C:\Data\MyDB12.mdf','C:\Data\MyDB13.mdf',
'C:\Data\MyDB14.mdf','C:\Data\MyDB15.mdf',
'C:\Data\MyDB16.mdf','C:\Data\MyDB17.mdf',
'C:\Data\MyDB18.mdf'
結果:
Msg 8144, Level 16, State 2, Procedure sp_attach_db, Line 0
Procedure or function sp_attach_db has too many arguments specified.
因為我們將附加更多的文件,所以我們不使用sp_attach_single_file_db系統存儲過程。所以讓我們使用帶有“for ATTACH_REBUILD_LOG”的“Create database”語句試著附加它們。執行下面的事務SQL語句。
CREATE DATABASE MyDB1
ON
(FILENAME = 'C:\Data\MyDB1.mdf'),
(FILENAME = 'C:\Data\MyDB2.mdf'),
(FILENAME = 'C:\Data\MyDB3.mdf'),
(FILENAME = 'C:\Data\MyDB4.mdf'),
(FILENAME = 'C:\Data\MyDB5.mdf'),
(FILENAME = 'C:\Data\MyDB6.mdf'),
(FILENAME = 'C:\Data\MyDB7.mdf'),
(FILENAME = 'C:\Data\MyDB8.mdf'),
(FILENAME = 'C:\Data\MyDB9.mdf'),
(FILENAME = 'C:\Data\MyDB10.mdf'),
(FILENAME = 'C:\Data\MyDB11.mdf'),
(FILENAME = 'C:\Data\MyDB12.mdf'),
(FILENAME = 'C:\Data\MyDB13.mdf'),
(FILENAME = 'C:\Data\MyDB14.mdf'),
(FILENAME = 'C:\Data\MyDB15.mdf'),
(FILENAME = 'C:\Data\MyDB16.mdf'),
(FILENAME = 'C:\Data\MyDB17.mdf'),
(FILENAME = 'C:\Data\MyDB18.mdf')
for ATTACH_REBUILD_LOG
GO
結果:
File activation failure. The physical file name "C:\Data\MyDB1.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB2.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB3.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB4.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB5.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB6.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB7.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB8.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB9.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB10.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB11.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB12.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB13.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB14.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB15.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB16.ldf" may be incorrect.
File activation failure. The physical file name "C:\Data\MyDB17.ldf" may be incorrect.
New log file 'C:\Data\MyDB1_log.LDF' was created.
本篇文章介紹了在附加多個.MDF文件時怎樣重建日志。
關鍵字:SQL server、日志、附加
新文章:
- CentOS7下圖形配置網絡的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統后丟失windows啟動項
- CentOS單網卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網打印機IP講解
- CentOS7使用hostapd實現無AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網絡重啟出錯
- 解決Centos7雙系統后丟失windows啟動項
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統有什么不同呢
- Centos 6.6默認iptable規則詳解