CMD 命令列操作 SQL Server 2005 完整指令彙總

連線指令

sqlcmd -S <伺服器位址> -d <資料庫名稱> -U <使用者> -P <密碼>

基礎管理指令

-- 0. 建立資料庫
CREATE DATABASE DB001;

-- 1. 建立登入帳號
CREATE LOGIN USER1 WITH PASSWORD = 'USER_PW';

-- 2. 變更資料庫擁有者
USE DB001;
EXEC sp_changedbowner 'USER1';
GO

-- 3. 啟用 READ_COMMITTED_SNAPSHOT
ALTER DATABASE [DB001] SET READ_COMMITTED_SNAPSHOT ON;
GO

-- 4. 修改定序
ALTER DATABASE DB001 COLLATE SQL_Latin1_General_CP437_CS_AS;
GO

-- 5. 列出所有資料庫
SELECT name FROM master..sysdatabases;

-- 6. 列出所有使用者資料表
SELECT name FROM sysobjects WHERE xtype = 'U';
-- xtype='S' 為系統表

-- 7. 列出指定表欄位
SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName');

-- 8. 搜尋相關物件（View/SP/Function）
SELECT a.* FROM sysobjects a, syscomments b 
WHERE a.id = b.id AND b.text LIKE '%TableName%';

-- 9. 查詢欄位與資料型別
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = N'TableName';

-- 10. 取得資料/記錄檔路徑
SELECT LTRIM(RTRIM(filename)) FROM dbname..sysfiles WHERE CHARINDEX('MDF', filename) > 0;
SELECT LTRIM(RTRIM(filename)) FROM dbname..sysfiles WHERE CHARINDEX('LDF', filename) > 0;

備份與還原指令大全

完整備份

BACKUP DATABASE NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Full_20070908.bak';

差異備份

BACKUP DATABASE NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Diff_20070908.bak'
WITH DIFFERENTIAL;

交易記錄備份

-- 一般（截斷記錄）
BACKUP LOG NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Log_20070908.bak';

-- 不截斷（異地備份前）
BACKUP LOG NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Log_20070908.bak'
WITH NO_TRUNCATE;

-- 截斷不備份
BACKUP LOG NorthwindCS WITH NO_LOG;
-- 或
BACKUP LOG NorthwindCS WITH TRUNCATE_ONLY;

檔案/檔案組備份

-- 檔案備份
EXEC sp_helpdb NorthwindCS;
BACKUP DATABASE NorthwindCS
FILE = 'NorthwindCS'
TO DISK = 'G:\Backup\NorthwindCS_File_20070908.bak';

-- 檔案組備份
BACKUP DATABASE NorthwindCS
FILEGROUP = 'Primary'
TO DISK = 'G:\Backup\NorthwindCS_FileGroup_20070908.bak'
WITH INIT;

進階備份策略

-- 分割備份（多目標，還原需全數）
BACKUP DATABASE NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Full_1.bak',
   DISK = 'G:\Backup\NorthwindCS_Full_2.bak';

-- 鏡像備份（每份相同，提高可靠度）
BACKUP DATABASE NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Mirror_1.bak'
MIRROR TO DISK = 'G:\Backup\NorthwindCS_Mirror_2.bak'
WITH FORMAT;

-- 鏡像到本地與遠端
BACKUP DATABASE NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Mirror_1.bak'
MIRROR TO DISK = '\\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak'
WITH FORMAT;

-- 每日產生唯一檔名
DECLARE @Path NVARCHAR(2000);
SET @Path = 'G:\Backup\NorthwindCS_Full_' + CONVERT(NVARCHAR, GETDATE(), 112) + '.bak';
BACKUP DATABASE NorthwindCS TO DISK = @Path;

還原與驗證

-- 從 NORECOVERY/STANDBY 復原
RESTORE DATABASE NorthwindCS_Bak WITH RECOVERY;

-- 檢視備份組
RESTORE HEADERONLY FROM DISK = 'G:\Backup\NorthwindCS_Full_20070908.bak';

-- 檢視檔案清單
RESTORE FILELISTONLY FROM DISK = 'G:\Backup\NorthwindCS_Full_20070908_2.bak' WITH FILE = 1;

-- 檢視卷標
RESTORE LABELONLY FROM DISK = 'G:\Backup\NorthwindCS_Full_20070908_2.bak';

-- 密碼保護備份
BACKUP DATABASE NorthwindCS
TO DISK = 'G:\Backup\NorthwindCS_Full_20070908.bak'
WITH PASSWORD = '123', INIT;

RESTORE DATABASE NorthwindCS
FROM DISK = 'G:\Backup\NorthwindCS_Full_20070908.bak'
WITH PASSWORD = '123';