sqlserver備份兩種方式

1. 通過維護計劃

2. 通過job運作指令

--差異備份
EXECUTE master.dbo.xp_create_subdir N'D:\pic\RE\TEST'
GO
BACKUP DATABASE [TEST] TO DISK = N'D:\pic\RE\TEST\TEST_backup_201101051801.bak' 
WITH DIFFERENTIAL , NOFORMAT, NOINIT, 
NAME = N'TEST_backup_20110105180152', SKIP, REWIND, NOUNLOAD, STATS = 10
go

--完整備份
BACKUP DATABASE [TEST] TO DISK = N'D:\pic\RE\TEST_backup_201101051802.bak' 
WITH NOFORMAT, NOINIT, 
NAME = N'TEST_backup_20110105180256', SKIP, REWIND, NOUNLOAD, STATS = 10
go

--強制還原
--REPLACE覆寫已存在資料庫
RESTORE DATABASE TEST 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\TEST.BAK'
WITH MOVE 'TEST' TO 'D:\pic\TEST.mdf', 
MOVE 'TEST_log' TO 'D:\pic\TEST_log.ldf',
STATS = 10, REPLACE
GO

--顯示備份清單
RESTORE FILELISTONLY 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\TEST.BAK'
go

生成年月日的備份檔案

declare @databasename nvarchar(50)
set @databasename = 'ExtDB'
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'E:\工作目錄\bk\' + @[email protected] + '.bak'
BACKUP DATABASE @databasename TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT