SQL Server 备份到网络盘网络映射盘

declare @DBName nvarchar(max)
declare @BakName nvarchar(max)
--在这里修改数据库名称
select @DBName='[LFBMP.PO]'
--指定保存路径\\192.168.100.2\d$\DBBak
select @BakName='\\192.168.100.2\d$\DBBak\aa'+REPLACE(@DBName,'.','_')+Convert(char(10),getdate(),120)+'.bak'
--启用网络映射盘,(以下的语句只要执行一次就可以了,以后备份的时候就需要再执行了）
--exec master..xp_cmdshell 'net use \\192.168.1.2\d\DBBak password /user:192.168.1.2\administrator'  --添加映射盘
--exec master..xp_cmdshell 'net use \\192.168.1.2\d\DBBak /delete' ----删除映射盘
--本地磁盘
--select @BakName='D:\dbbak\'+REPLACE(@DBName,'.','_')+Convert(char(10),getdate(),120)+'.bak'

declare @BakSQL nvarchar(max)
select @BakSQL='Backup Database '+@DBName+' To disk='''+@BakName+''' with init,nounload '
print @BakSQL

exec (@BakSQL)


--启用xp_cmdshell(exec master..xp_cmdshell报错就用下面的)
--EXEC sp_configure 'show advanced options', 1;
--RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;
--RECONFIGURE;

　　

 

再放一个一次备份多个表的

declare @DBName nvarchar(max)
declare @BakName nvarchar(max)

Declare DBCursor CURSOR 
FORWARD_ONLY READ_ONLY 
For (select '[DB1]' as DBName
union select '[DB2]' as DBName 
union select '[DB3]' as DBName 
union select '[DB4]' as DBName --还有多个数据库在后面添加
)
open DBCursor
fetch next from DBCursor into @DBName
while (@@fetch_status=0)
    begin
    --print @DBName
    --select @DBName='[LFBMP.PO]'
    --指定保存路径\\192.168.100.2\d$\DBBak    
    --select @BakName='D:\MSSQLDBBak\BC_'+REPLACE(@DBName,'.','_')+Convert(char(10),getdate(),120)+'.bak'
    select @BakName='\\192.168.100.2\d$\DBBak\'+@DBName+Convert(char(10),getdate(),120)+'.bak'
    declare @BakSQL nvarchar(max)
    --select @BakSQL='Backup Database '+@DBName+' To disk='''+@BakName+''''
    select @BakSQL='Backup Database '+@DBName+' To disk='''+@BakName+'''  with init,nounload '
    print @BakSQL
    exec (@BakSQL)
    fetch next from DBCursor into @DBName
    end
close DBCursor
deallocate DBCursor

--select @DBName='[LFBMP.PO]'
----指定保存路径\\192.168.100.2\d$\DBBak
----select @BakName='\\192.168.100.2\d$\DBBak\aa'+REPLACE(@DBName,'.','_')+Convert(char(10),getdate(),120)+'.bak'
--select @BakName='D:\MSSQLDBBak\BC_'+REPLACE(@DBName,'.','_')+Convert(char(10),getdate(),120)+'.bak'

--declare @BakSQL nvarchar(max)
--select @BakSQL='Backup Database '+@DBName+' To disk='''+@BakName+''''
--print @BakSQL