backupdbproc.sql

对于sql server2005数据库中

-- 1.创建存储过程BackupDBProc
use dbas52
IF EXISTS (SELECT name FROM sysobjects WHERE  name = N'BackupDBProc' AND type = 'P')
   DROP PROCEDURE BackupDBProc
GO
CREATE PROCEDURE BackupDBProc
    @DataBaseName varchar(100),
    @FolderPath varchar(1000)
AS
    declare @filePath varchar(1000)
    declare @cmd varchar(1000)
 
    set @filePath=@FolderPath + @DataBaseName + '-' + convert(varchar(20), GETDATE(), 112) + replace(convert(nvarchar(20),getdate(),108),':','') + '.bak'
    set @cmd = 'bcp ' + @DataBaseName + '.dbo.t_JSClipInfo out ' + @filePath + ' -c -U"sa" -P"123"'
    exec master.dbo.xp_cmdshell @cmd