資料庫備份命令

CREATE PROCEDURE [dbo].[DatabaseBackup]
@dbname nvarchar(30),
@path varchar(100)
AS
BEGIN

declare @filename nvarchar(60)
declare @weekdayid varchar(1)
declare @db_cnt int

SELECT @weekdayid=DATEPART(weekday, GETDATE())

select @db_cnt=count(*) from master..sysdatabases where name=@dbname
if @db_cnt=0
begin
raiserror(N'資料庫名稱錯誤。', 16, 1)
goto TheEnd
end

if @weekdayid='1'
begin
select @filename=@path+'\'+@dbname+'_Sun.dat'
end

if @weekdayid='2'
begin
select @filename=@path+'\'+@dbname+'_Mon.dat'
end
.
.
.
if @weekdayid='7'
begin
select @filename=@path+'\'+@dbname+'__Sat.dat'
end

BACKUP DATABASE @dbname TO DISK = @filename WITH NOFORMAT, INIT, NAME = @filename, SKIP, NOUNLOAD, STATS = 10

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@dbname )
if @backupSetId is null
begin
raiserror(N'確認失敗。找不到資料庫的備份資訊。', 16, 1)
goto TheEnd
end

RESTORE VERIFYONLY FROM DISK = @filename WITH FILE = @backupSetId, NOUNLOAD

TheEnd:

END