黃昏的甘蔗

1. 建立檔案群組 FileGroup
2. 建立對應的次資料檔案 （全部都放在 mdf 沒意義吧！）
3. 建立 Partition Function (決定切割欄位與分割點）
4. 建立 Partition Scheme （搭配 PF 決定指向哪些 FG）
5. 建立資料表，並使用 ON 子句表示套用哪一個 PS

插入資料
-- Insert into ... Select ... From
INSERT INTO dbo.New_Customers SELECT UserID, UserName FROM Customers

-- Select ... into ... From ...
SELECT * INTO New_Customers FROM Customers

PS：在 Recovery Mode 為 Bulk 時，這兩種操作方式，將不會產生 Transaction Log
PS2：Log 太大請參考 http://blog.xuite.net/tolarku/blog/41939197

• DBCC SHRINKDATABASE (New_db,1)
• DBCC SHRINKFILE (New_db,2)

USE master
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'NEW_db')
  BEGIN
    SELECT '資料庫已存在'
    -- DROP DATABASE [New_db]
  END
ELSE
  BEGIN
    SELECT '資料庫{不}存在'
    --CREATE DATABASE [New_db] -- 都不指定時會依系統 model DB 來建立，檔案會放在安裝SQL時所指定的資料路徑，沒改的話會在「C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA」
  END
GO

-- 備份資料庫
BACKUP DATABASE NEW_db TO DISK ='C:\DBBackup\NEW_db.bak'
WITH INIT, STATS=25, COMPRESSION –- 使用壓縮

-- 還原資料庫
RESTORE DATABASE NEW_db
FROM  DISK = 'C:\DBBackup\NEW_db.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

USE [master]
GO
EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'New_db', @physicalname = N'D:\DB\New_db.bak'

-- 也可以從SSMS來操作


-- 在選取備份目的地時就選[備份裝置]

-- 有什麼好處呢？

-- 你每次備份的時候，都必須給予不同的備份檔名稱，通常會以日期時間來做區隔，但是如果你每半小時做一次 Transaction Log Backup ，那意思就是說一天會有48個 Log 檔，你要救援還原時，就必須將那些 Log backup 一個一個倒回來，還得手動改檔案名稱～～麻煩阿
-- 使用備份裝置時，你便可以輕鬆的點選你要還原的備份有哪些，勾一勾就搞定了



RESTORE DATABASE [Northwind] FROM  DISK = N'D:\DB\New_db.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [Northwind] FROM  DISK = N'D:\DB\New_db.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'D:\DB\New_db.bak' WITH  FILE = 3,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'D:\DB\New_db.bak' WITH  FILE = 4,  NOUNLOAD,  STATS = 10
GO

SELECT * FROM sys.backup_devices –- 備份裝置是屬於整體資料庫，非專屬於哪個使用者資料庫，可以用這行來查看目前有哪些

-- 單獨還原某一份 dump device 裡的 備份檔
RESTORE FILELISTONLY FROM new_db WITH FILE = 3

-- 在 SQLCMD 模式下執行


--停止SQL Server Service

!!net stop mssqlserver /Y
-- 執行Dos command 命令
!!copy "C:\DB\Northwind.*"  "D:\temp" 
-- 執行SQL Server Service
!!net start mssqlserver /Y

建立快照
CREATEDATABASE Northwind_S1
ON ( NAME = N'Northwind', FILENAME = N'C:\myAdmin\DB\Northwind_S1.SS')
AS SNAPSHOT OF Northwind

SSMS 介面會出現在


查詢 Snapshot DB 所佔用之大小
SELECTDB_NAME(DbId) AS '快照資料庫',BytesOnDisk/1024.0 AS '檔案大小(KB)' FROMfn_virtualfilestats(DB_ID(N'Northwind_S1'), NULL);

查詢資料庫與其 Snapshot DB 對應關係


SELECTname N'資料庫名稱', database_id N'資料庫識別碼', source_database_id N'Snapshot的來源資料庫識別碼'
FROMsys.databases

刪除快照資料庫
DROPDATABASE northwind_S1

-- 在 Northwind 上建立 tolarku 帳號，密碼為 showmemoney
USE[master]
GO
CREATE LOGIN [tolarku] WITH PASSWORD=N'showmemoney', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

-- 同上，再給予 server role 的 sysadm 最大權限

USE[master]
GO
CREATE LOGIN [tolarku] WITH PASSWORD=N'showmemoney', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember@loginame = N'tolarku', @rolename = N'sysadmin'

-- 同上，再再給予 tolarku 擁有 Northwind 的 db_owner 權限

*** 請注意：雖然第三個動作是多餘的（因為都已經擁有 sysadm 的最大權限了），但是主要的目的是要觀察，對於某一使用者增加其 Server Role 與 增加 Database Role 的不同～

*** 當增加 Database Role 時，會進入（use）該資料庫，才用 sp_addrolemember 來設定
USE[master]
GO
CREATE LOGIN [tolarku] WITH PASSWORD=N'showmemoney', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'tolarku', @rolename = N'sysadmin'
GO
USE [Northwind]
GO
CREATE USER [tolarku] FOR LOGIN [tolarku]
GO
USE [Northwind]
GO
EXEC sp_addrolememberN'db_owner', N'tolarku'

-*-*-*---*-*--*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

*** 再請觀察另外一件事，就是不管增加 Server Role 或 Database Role 時，就只有指定「誰～帳號」跟「身分～權限」，跟所用的密碼一點都沒關係

*** 你會說，很合理阿～帳密的訊息本來就是在整個 Server 上，不會個別的資料庫去設定密碼......

那麼！想像一下，當資料庫經過「備份」與「還原」到其他 Server 時，他的帳密部分會不會跟著帶過去呢？那在整個 Server 上的資料也會跟著過去嗎？

沒有～記錄在個別資料庫的訊息,,,,,,當然也帶不過去

所以～還原到其他 Server 時，是會發生 SQL 帳號無法正常登入

解決方法....請參考「MS-SQL 資料庫還原到另一台主機無法登入」

-- 建立群組 DataBase Role ，並把權限加入此群組身份中



USE[Northwind]
CREATE ROLE [RD_staff]
GRANT SELECT ON [dbo].[customers_BAK] TO [RD_staff]
GRANT UPDATE ON [dbo].[CustomerDemographics] TO [RD_staff]
GRANT SELECT ON [dbo].[CustomerDemographics] TO [RD_staff]
GRANT INSERT ON [dbo].[CustomerCustomerDemo] TO [RD_staff]
GRANT UPDATE ON [dbo].[Customers] TO [RD_staff]
GRANT INSERT ON [dbo].[Customers] TO [RD_staff]
GRANT SELECT ON [dbo].[Customers] TO [RD_staff]

-- 建立一個「hammer」使用者，預設不給任何權限，只建立帳號
USE[master]
CREATE LOGIN [hammer] WITH PASSWORD=N'1111', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

-- 把剛剛建立的 RD_staff 所擁有的權限，指派給新人「hammer」


USE[Northwind]
CREATE USER [hammer] FOR LOGIN [hammer]
USE [Northwind]
EXEC sp_addrolememberN'RD_staff', N'hammer'

-- 測試該帳號是否能登入，並且對「Northwind」的「Customers」有 I/S/U 的權限
嗯嗯～可以登入，但是不能存取 Employees （當然囉！我們又沒給他這個物件的權限）


-- 對有權限的物件，就可以正常的存取，當然 update 也行

-- 以後維護某單位下的使用帳號，就不要再個別的設定，只要使用群組，就可以輕鬆管理
-- 當然～使用 schema 來管理也很好用

-- QQ 那如果又要針對某個 user 給予某個物件特別的權限，可以嗎？
-- AA：當然可以囉，就用一般的權限指派方式就可以了～～是可以並存的。
use[Northwind]
GRANT DELETE ON [dbo].[Employees] TO [hammer]
GRANT UPDATE ON [dbo].[Employees] TO [hammer]
GRANT INSERT ON [dbo].[Employees] TO [hammer]
GRANT SELECT ON [dbo].[Employees] TO [hammer]

-- 上面那樣四行的指令是用「指令碼」產生出來的，自己打 T-SQL 不用那麼麻煩，用逗點隔開就可以了

GRANTSELECT,INSERT, UPDATE,DELETE ON [dbo].[customers_BAK] TO [RD_staff]

-- 查詢 資料庫物件 的授權情形


SELECTpm.major_id, pm.type,pc.name 'Principal_Name'
,pm.permission_name, pm.state
FROM sys.database_permissions pm
inner join sys.database_principals pc
on pm.grantee_principal_id = pc.principal_id
WHERE major_id = OBJECT_ID('[Employees]')

-- Application Role 的使用方式跟 Database role 順序差不多，但應用程式角色是使用 sp_setapprole (需要有密碼) 予以啟用，MSDN說明可能會有安全性上的疑慮，那就不要用吧！http://msdn.microsoft.com/zh-tw/library/ms181127.aspx