Create procedure sp_backdb @ dbname varchar ( 100 ), @ Path varchar ( 100 )
As
-- Create by Allen 2004 - 11 - 01 In Order to backup database and verify Database
Declare @ bakname varchar ( 100 )
-- Declare @ dbname varchar ( 100 )
Declare @ SQL varchar ( 8000 )
-- Declare @ sql1 varchar ( 8000 )
-- Declare @ path varchar ( 100 )
-- Set @ Path = ' C :\ '
-- Set @ Dbname = ' Northwind '
Set @ Bakname = @ Dbname + Cast (datepart (weekday, getdate ()) - 1 As Varchar ( 1 ))
-- The backup files from Sunday to Saturday are bak0 to bak6.
Set @ SQL = ' Backup database [ ' + @ Dbname + ' ] To disk = N ''' + @ Path + '' + @ Bakname + ' . Bak '' With init, nounload, noskip, stats = 10, noformat '
-- Print @ SQL
Set @ SQL = @ SQL + ' Declare @ I int
Select @ I = Position from MSDB .. backupset where database_name = ''' + @ Dbname + ''' And type ! = ''' + ' F ''' + '
And backup_set_id = (Select max (backup_set_id) from MSDB .. backupset where database_name = ''' + @ Dbname + ''' )
Restore verifyonly from disk = N ''' + @ Path + '' + @ Bakname + ' . Bak ''' + ' With File = @ I '
-- Print @ SQL
Exec (@ SQL)
Back up the data to the specified directory and cycle from Sunday to Saturday (0---6)
