SQL SERVER几种备份与删除数据库备份的方法

------------------本地备分数据库---------------------------
--备份数据库
declare @filename nvarchar(100)
set @filename='E:/backup/cr_ypls8'+ltrim(rtrim(convert(char(10),getdate(),112)))+'.bak'
print @filename
BACKUP DATABASE cr_ypls8 TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'备份', NOSKIP , STATS = 10, NOFORMAT

--删除前天的数据库
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
 RECONFIGURE
declare @sql varchar(500)
select @sql='del E:/backup/cr_ypls8'+ltrim(rtrim(convert(char(10),getdate()-2,112)))+'.bak'
print @sql
exec master..xp_cmdshell @sql
go
EXEC sp_configure 'xp_cmdshell', 0
 RECONFIGURE
-------------------------------------------------------------

 

------------------------异地备份数据库----------------------------------------------------------------
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
---异地备份SQL
--//192.168.1.26/backup 共享目录
--//192.168.1.26/backup "" /user:A00FCA5D873F4D8/administrator'备份文件服务器机器名,用户和密码
declare @sql varchar(500)
select @sql='//192.168.1.26/backup/test'+'_db_'+convert(varchar(10),getdate(),112)+substring(convert(varchar(20),getdate(),108) ,1,2)+'.bak'

exec master..xp_cmdshell 'net use //192.168.1.26/backup "" /user:A00FCA5D873F4D8/administrator'

backup database test to disk=@sql  
go
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

---异地备份删除 SQL
EXEC sp_configure 'show advanced options', 1
reconfigure
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
declare @sql varchar(500)
select @sql='del'+'//192.168.1.26/backup/test'+'_db_'+convert(varchar(10),dateadd(day,-7,getdate()),112)+substring(convert(varchar(20),dateadd(day,-7,getdate()),108) ,1,2)+'.bak'
print @sql
exec master..xp_cmdshell @sql --删除七天前作的备份。
go
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE

----------------------------------------------------------------------------------------------------