SQL2005 Automatic backup Script _mssql2005

This version is always only the latest version (my own version is a daily backup)

Copy Code code as follows:

DECLARE @DBName varchar (200)
Set @DBName = ' reportserver$sql2005 '
--Truncate Log
DUMP TRANSACTION @DBName with no_log
--Shrinking the database
DBCC shrinkdatabase (@DBName, truncateonly)
--Backing up the database
Use master
DECLARE @Version varchar (20)
DECLARE @DateAppend varchar (20)
DECLARE @BasePath varchar (200)
DECLARE @BakPath varchar (200)
--Set the base directory for backup
Set @BasePath = ' f:\tmp '
--set version, each version of the backup put in a different place
Set @Version = ' V6.1 '
--Set the full path of the backup
Set @BakPath = @BasePath + ' + ' + @Version + ' \db.bak '
Use master
--Create a backup device, if present, without establishing
if exists (SELECT * from sysdevices where name= ' Ctos_db_bak ')
Begin
EXEC sp_dropdevice ' Ctos_db_bak '
DECLARE @tmpcmd varchar (100)
Set @tmpcmd = ' del ' + @BakPath
EXEC sp_configure ' show advanced options ', 1
Reconfigure
EXEC sp_configure ' xp_cmdshell ', 1
Reconfigure
EXEC master.. xp_cmdshell @tmpcmd
EXEC sp_configure ' show advanced options ', 1
Reconfigure
EXEC sp_configure ' xp_cmdshell ', 0
Reconfigure
End
EXEC sp_addumpdevice ' disk ', ' Ctos_db_bak ', @BakPath
--Backing up the database
BACKUP DATABASE @DBName to Ctos_db_bak