Tweet |
|
最近工作遇到一個有點棘手的狀況,由於需要更新客戶端的部份表格 (因部份表格客戶已新增資料)
麻煩的是表格之間還有關聯關系 (Foreign Key) ,且表格有設定
使用的工具如下
Microsoft SQL Server Management Studio
MS SQL Express
以下步驟可以順利完成備份 / 還原表格之需求
以下假設需要匯入資料的 DB 為 DB – import ,而被匯入資料的 Table 為 DB- export
總而言之目前遇到以下四個狀況
(1) 僅備份 / 還原 DB – import 的部份表格
(2) DB – import 的表格之間有關聯
(3) DB – import 的表格的 PK 為唯讀
(4) DB – import 與 DB- export 在不同主機
在摸索了一陣子之後,找出還原的步驟如下
(1) 先在 DB- import 的 SQL主機開啟另一個 DB -temp,將 DB- export 的資料 (部份 Table ) 匯入 DB –temp ,目前已將 DB –temp 與 DB-import 置放在同一主機
(2) 備份 DB- import 的所有關聯關係
(3) 刪除 DB- import 的所有關聯關係
(4) 清空 DB- import 的 Table 內容
(5) 執行匯入精靈
(6) 利用剛剛備份好的關聯關係,重新建立關聯
以下將用圖示的方式依序完成任務
(1) 先在 DB- import 的 SQL主機開啟另一個 DB -temp,將 DB- export 的資料 (部份 Table ) 匯入 DB –temp ,目前已將 DB –temp 與 DB-import 置放在同一主機
此部份使用內建的備份 / 還原即可
(2) 備份 DB- import 的所有關聯關係
此部份是參考 http://blog.sqlauthority.com/2008/04/18/sql-server-generate-foreign-key-scripts-for-database/ 這個人的解法
請在要備份關聯的資料庫上面按右鍵,選擇新增查詢
輸入以下程式碼
EXEC dbO.SPGetForeignKeyInfo IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPGetForeignKeyInfo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.SPGetForeignKeyInfo GO CREATE PROCEDURE dbo.SPGetForeignKeyInfo AS /* Author : Seenivasan This procedure is used for Generating Foreign Key script. */ SET NOCOUNT ON DECLARE @FKName NVARCHAR(128) DECLARE @FKColumnName NVARCHAR(128) DECLARE @PKColumnName NVARCHAR(128) DECLARE @fTableName NVARCHAR(128) DECLARE @fUpdateRule INT DECLARE @fDeleteRule INT DECLARE @FieldNames NVARCHAR(500) CREATE TABLE #Temp( PKTABLE_QUALIFIER NVARCHAR(128), PKTABLE_OWNER NVARCHAR(128), PKTABLE_NAME NVARCHAR(128), PKCOLUMN_NAME NVARCHAR(128), FKTABLE_QUALIFIER NVARCHAR(128), FKTABLE_OWNER NVARCHAR(128), FKTABLE_NAME NVARCHAR(128), FKCOLUMN_NAME NVARCHAR(128), KEY_SEQ INT, UPDATE_RULE INT, DELETE_RULE INT, FK_NAME NVARCHAR(128), PK_NAME NVARCHAR(128), DEFERRABILITY INT) DECLARE TTableNames CURSOR FOR SELECT name FROM sysobjects WHERE xtype = 'U' OPEN TTableNames FETCH NEXT FROM TTableNames INTO @fTableName WHILE @@FETCH_STATUS = 0 BEGIN INSERT #Temp EXEC dbo.sp_fkeys @fTableName FETCH NEXT FROM TTableNames INTO @fTableName END CLOSE TTableNames DEALLOCATE TTableNames SET @FieldNames = '' SET @fTableName = '' SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName, @FieldNames AS FTFields,PKTABLE_NAME AS STName, @FieldNames AS STFields,@FieldNames AS FKType INTO #Temp1 FROM #Temp ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME DECLARE FK_CUSROR CURSOR FOR SELECT FKName FROM #Temp1 OPEN FK_CUSROR FETCH FROM FK_CUSROR INTO @FKName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE FK_FIELDS_CUSROR CURSOR FOR SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE FROM #TEMP WHERE FK_NAME = @FKName ORDER BY KEY_SEQ OPEN FK_FIELDS_CUSROR FETCH FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName, @fUpdateRule,@fDeleteRule WHILE @@FETCH_STATUS = 0 BEGIN UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields) = 0 THEN '['+@FKColumnName+']' ELSE FTFields +',['+@FKColumnName+']' END WHERE FKName = @FKName UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields) = 0 THEN '['+@PKColumnName+']' ELSE STFields +',['+@PKColumnName+']' END WHERE FKName = @FKName FETCH NEXT FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName, @fUpdateRule,@fDeleteRule END UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0 THEN FKType + ' ON UPDATE CASCADE' ELSE FKType END WHERE FKName = @FKName UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0 THEN FKType + ' ON DELETE CASCADE' ELSE FKType END WHERE FKName = @FKName CLOSE FK_FIELDS_CUSROR DEALLOCATE FK_FIELDS_CUSROR FETCH next FROM FK_CUSROR INTO @FKName END CLOSE FK_CUSROR DEALLOCATE FK_CUSROR SELECT 'ALTER TABLE [dbo].['+FTName+'] ADD CONSTRAINT ['+FKName+'] FOREIGN KEY ('+FTFields+') REFERENCES ['+STName+'] ('+STFields+') '+FKType FROM #Temp1 SET NOCOUNT OFF RETURN GO
如此一來即可在 output 視窗取得關聯的 Scipt ,按 ctrl + A 複製另存即可
(3) 刪除 DB- import 的所有關聯關係
此部份則是參考 http://mafudge.mysite.syr.edu/2010/05/07/dropping-all-the-foreign-keys-in-your-sql-server-database/ 這篇文章所述
主要可用以下的指令將某個資料庫的所有關聯去除
-- begin script DECLARE @cmd nvarchar(1000) DECLARE @fk_table_name nvarchar(1000) DECLARE @fk_name nvarchar(1000) DECLARE cursor_fkeys CURSOR FOR SELECT OBJECT_NAME(fk.parent_object_id) AS fk_table_name, fk.name as fk_name FROM sys.foreign_keys fk JOIN sys.tables tbl ON tbl.OBJECT_ID = fk.referenced_object_id -- WHERE OBJECT_NAME(fk.parent_object_id) in ('table1', 'table2', 'table2') OPEN cursor_fkeys FETCH NEXT FROM cursor_fkeys INTO @fk_table_name, @fk_name WHILE @@FETCH_STATUS=0 BEGIN -- build alter table statement SET @cmd = 'ALTER TABLE [' + @fk_table_name + '] DROP CONSTRAINT [' + @fk_name + ']' -- execute it exec dbo.sp_executesql @cmd FETCH NEXT FROM cursor_fkeys INTO @fk_table_name, @fk_name END CLOSE cursor_fkeys DEALLOCATE cursor_fkeys
(4) 清空 DB- import 的 Table 內容
此部份則直接下 SQL 指令即可,例如:
TRUNCATE TABLE A; TRUNCATE TABLE B;
(5) 執行匯入精靈
現在需要把剛剛備份過的 DB-temp 資料匯入目標 DB-import ,請執行匯入資料
選擇匯入來源 (在此例資料庫為 DB-temp)
選擇匯出來源 (在此例資料庫為 DB-import)
選擇從資料表取出資料
選擇對應的資料表
按完成開始匯入
成功後關閉
(6) 利用剛剛備份好的關聯關係,重新建立關聯
此部份則用之前備份好的關聯指令
執行查詢
完成
可在資料庫上面按右鍵,選擇檢視相依性來看關聯是否已回復
完成!
