[MSSQL] 備份/還原部份表格

【如果覺得文章寫的好的話希望能按一下讚或分享喔】

image

 

最近工作遇到一個有點棘手的狀況,由於需要更新客戶端的部份表格  (因部份表格客戶已新增資料)

麻煩的是表格之間還有關聯關系 (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 置放在同一主機

此部份使用內建的備份 / 還原即可

image

 

(2) 備份 DB- import  的所有關聯關係

此部份是參考 http://blog.sqlauthority.com/2008/04/18/sql-server-generate-foreign-key-scripts-for-database/ 這個人的解法

請在要備份關聯的資料庫上面按右鍵,選擇新增查詢

image

 

輸入以下程式碼

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 複製另存即可

image

 

(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 ,請執行匯入資料

image

 

選擇匯入來源 (在此例資料庫為 DB-temp)

image

 

選擇匯出來源 (在此例資料庫為 DB-import)

image

 

選擇從資料表取出資料

image

 

選擇對應的資料表

image

 

按完成開始匯入

image

 

成功後關閉

 

(6) 利用剛剛備份好的關聯關係,重新建立關聯

此部份則用之前備份好的關聯指令

image

 

執行查詢

image

 

完成

image

 

可在資料庫上面按右鍵,選擇檢視相依性來看關聯是否已回復

image

image

 

完成!

Related Posts Plugin for WordPress, Blogger...
【如果覺得文章寫的好的話希望能按一下讚或分享喔】

彭其捷

想要做出很棒的產品,所以我寫程式,也研究介面 (UI) 與使用者經驗 (UX),並思考管理的法則。

【Profile】http://about.me/divaka

twitter facebookgooglepluslinkedinbloggertumblrflickryoutubedribbblerssemail


Fatal error: Uncaught Exception: 12: REST API is deprecated for versions v2.1 and higher (12) thrown in /home/content/63/7692663/html/blog/wp-content/plugins/seo-facebook-comments/facebook/base_facebook.php on line 1273