淺談如何在SQL Server 2008中附加一個數據庫
51CTO向您推薦《SQL Server 2008深度應用》,相信通過本專題,能讓您更深刻的了解SQL Server 2008。
在SQL Server 7.0中,微軟推出了sp_attach_db和sp_attach_single_file_db系統存儲過程。它對于SQL Server數據庫管理員執行下面的任務是非常方便的:
使用sp_attach_db系統存儲過程直接將.MDF和.LDF文件附加到服務器。
使用sp_attach_single_file_db系統存儲過程只附加.MDF文件。
使用sp_detach_db將數據庫從一個服務器分離,復制.MDF文件到另一個服務器上,然后使用sp_attach_db系統存儲過程重新附加這些文件到兩個服務器上。
盡管它對于SQL Server數據庫管理員是很有用的,但是在使用這兩個存儲過程時是有一些限制的。限制如下:
你不能附加多個日志文件
你不能附加16個以上的文件
在SQL Server 2008中,微軟宣布上面的系統存儲過程將在未來的版本中被廢棄。而他們在“Create Database”SQL語句中添加了一個從句“For Attach”。
這篇文章將介紹使用“For Attach”從句的多種方法,以克服在使用sp_attach_db和sp_attach_single_file_db時要面臨的限制。
假設我們有一個數據庫叫“MyDB1”,它有一個.MDF文件和一個.LDF文件。使用下面的事務SQL語句創建這個數據庫。再假設.MDF文件和.LDF文件的位置是“C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database DataData File for Monthly ArchiveMicrosoft SQL Server 2008 Data File”。
- Use Master
- go
- CREATE DATABASE MyDB1
- ON
- ( NAME = MyDB1_dat,
- FILENAME = 'C:Program Files
- Microsoft SQL ServerMSSQL10.SQL2008MSSQLDATA
- Summary Database DataData File for Monthly Archive
- Microsoft SQL Server 2008 Data FileMyDB1.mdf',
- SIZE = 10,
- MAXSIZE = 50,
- FILEGROWTH = 5 )
- LOG ON
- ( NAME = MyDB1_log,
- FILENAME = 'C:Program Files
- Microsoft SQL ServerMSSQL10.SQL2008MSSQLDATA
- Summary Database DataData File for Monthly Archive
- Microsoft SQL Server 2008 Data FileMyDB1.ldf',
- SIZE = 5MB,
- MAXSIZE = 25MB,
- FILEGROWTH = 5MB )
- GO
現在,讓我們分離該數據庫,并嘗試使用sp_detach_db和 sp_attach_db將它重新附加。執行下面的事務SQL語句。
- use master
- go
- sp_detach_db 'MyDB1'
- go
- sp_attach_db 'MyDb1',
- 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf',
- 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf'
- GO
- 你可以使用具有“For Attach”從句的“Create database”命令附加上相同的數據庫文件,如下所示。
- use master
- go
- sp_detach_db 'MyDB1'
- go
- CREATE DATABASE MyDB1
- ON
- (
- FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf'
- ),
- (
- FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf'
- ) for Attach
- go
現在,讓我們分離數據庫MyDB1,然后刪除.ldf文件,再然后使用sp_attach_single_file_db系統存儲過程通過執行下面的TSQL命令將它重新附加上。
- use master
- go
- sp_detach_db 'MyDB1'
- go
- exec master..xp_cmdshell 'del "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.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資源管理器的“Del”命令來刪除.ldf文件。
現在,讓我們只使用sp_attach_single_file_db來附加.MDF文件。執行下面所示的命令。
- use master
- go
- sp_attach_single_file_db 'MyDB1',
- 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf'
- go
結果
- File activation failure. The physical file name
- "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.ldf" may be incorrect.
- New log file 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF' was created.
你可以只通過使用帶有“For ATTACH_REBUILD_LOG”從句的“Create database”命令來附加相同的數據庫.MDF文件,如下所示。
- use master
- go
- sp_detach_db 'MyDB1'
- go
- exec master..xp_cmdshell 'del
- "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.ldf"'
- go
注意:當日志文件被重新創建時,SQL Server自動對日志文件名稱添加后綴“_log”。
- CREATE DATABASE MyDB1
- ON
- (
- FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database Data
- Data File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1.mdf'
- ) for ATTACH_REBUILD_LOG
結果:
- File activation failure. The physical file name "C:Program FilesMicrosoft SQL Server
- MSSQL10.SQL2008MSSQLDATASummary Database DataData File for
- Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF" may be incorrect.
- New log file 'C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASummary Database
- DataData File for Monthly ArchiveMicrosoft SQL Server 2008 Data FileMyDB1_log.LDF' was created.
總結
本文介紹了帶有“For Attach”和“for ATTACH_REBUILD_LOG”用于一個單獨的.MDF文件和一個單獨的.LDF文件的“Create Database”語句的使用。
【編輯推薦】
- SQL Server 2008行數據和頁數據壓縮解密
- 圖解SQL Server 2008 R2主數據服務安裝
- 理解SQL Server 2008索引的存儲結構
- SQL Server移動系統數據庫開發詳解
- SQL Server 2008 R2十大新特性解析