declare @TSQL varchar(4000), @db_name varchar(50), @DirectoryPath varchar(50), @Timestamp varchar(20)

set @DirectoryPath = 'D:\MSSQL\Backup\'
set @db_name = 'DASP-SIT'
set @Timestamp = convert(char(8), getdate(), 112) + replace(convert(varchar, getdate(), 108), ':', '')

set @TSQL = 'backup database [' + @db_name + '] to disk = '''
  + @DirectoryPath + @db_name + '_' + @Timestamp
  + '.bak'' WITH NOFORMAT, NOINIT, SKIP'

exec (@TSQL)

/*
====================================================
 2009-09-12 : Create by Jieh
 Description : 進行資料庫檔案備份 (壓縮)
====================================================
*/
-- step 1 backup database
declare @TSQL varchar(4000), @db_name varchar(50), @DirectoryPath varchar(50), @Timestamp varchar(20)
set @DirectoryPath = 'G:\DBBackup\'
set @Timestamp = convert(char(8), getdate(), 112) + replace(convert(varchar, getdate(), 108), ':', '')

-- select * from sysdatabases -- SQL2000 : sysdatabases, SQL2005 : sys.databases
declare Database_Cursor cursor for
  select name from sysdatabases
    join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

open Database_Cursor fetch next from Database_Cursor into @db_name
  while @@fetch_status = 0 begin -- print @db_name
    set @TSQL = 'backup database ' + @db_name + ' to disk = '''
      + @DirectoryPath + @db_name + '_' + @Timestamp
      + '.bak'' WITH NOFORMAT, NOINIT, SKIP'
    exec (@TSQL)
    fetch next from Database_Cursor into @db_name 
  end
close Database_Cursor deallocate Database_Cursor

-- step 1.1 successful log
declare @db_name varchar(50), @log varchar(1000)
set @log = '今日完成資料庫備份::'

declare Database_Cursor cursor for
  select name from sysdatabases
    join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

open Database_Cursor fetch next from Database_Cursor into @db_name
  while @@fetch_status = 0 begin
    set @log = @log + ' > ' + @db_name
    fetch next from Database_Cursor into @db_name 
  end
close Database_Cursor deallocate Database_Cursor
-- print @log
UPDATE [MIS_PROCESS].MIS.DBO.MIS_PROCESS_LIST SET
  MPL_FINAL_EXEC_DATE = convert(char(10), getdate(), 120),
  MPL_FINAL_EXEC_TIME = convert(char(8), getdate(), 108),
  MPL_FINAL_EXEC_RESULT = 'SUCCESSFUL',
  MPL_REMARK = @log
WHERE MPL_CODE = 'DBAM1101'

-- step 1.2 fail log
UPDATE [MIS_PROCESS].MIS.DBO.MIS_PROCESS_LIST SET
  MPL_FINAL_EXEC_DATE = convert(char(10), getdate(), 120),
  MPL_FINAL_EXEC_TIME = convert(char(8), getdate(), 108),
  MPL_FINAL_EXEC_RESULT = 'FAIL',
  MPL_REMARK = '資料庫備份失敗！'
WHERE MPL_CODE = 'DBAM1101'

-- step 1.3 MIS_PROCESS_HIRSTORY log
insert into [MIS_PROCESS].MIS.DBO.MIS_PROCESS_HISTORY
    (MPH_MPLCODE,MPH_EXEC_DATE,MPH_EXEC_TIME,MPH_RESULT,MPH_REMARK)

  select MPL_CODE,MPL_FINAL_EXEC_DATE,MPL_FINAL_EXEC_TIME,MPL_FINAL_EXEC_RESULT,MPL_REMARK
    from [MIS_PROCESS].MIS.DBO.MIS_PROCESS_LIST where MPL_CODE = 'DBAM1101'


-- step 2 shink database file
declare @TSQL varchar(4000), @db_name varchar(50), @shrink_name varchar(50)

declare db_cursor cursor for
  select name from sysdatabases
    join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

declare shrink_cursor cursor for
  select DB_SHRINKFILE_NAME from sysdatabases
    join DB_BACKUP_LIST on name = DB_NAME where EXEC_STATUS = 'ENABLE'

open db_cursor fetch next from db_cursor into @db_name
open shrink_cursor fetch next from shrink_cursor into @shrink_name
  while @@fetch_status = 0 begin -- print @db_name
    exec ('alter database ' + @db_name + ' set AUTO_SHRINK OFF')
    set @TSQL = 'use [' + @db_name + '] dbcc shrinkfile (N''' + @shrink_name + ''')'
    exec (@TSQL)
    fetch next from db_cursor into @db_name
    fetch next from shrink_cursor into @shrink_name
  end
close db_cursor deallocate db_cursor
close shrink_cursor deallocate shrink_cursor