一个备份作业总是报错
新建了一个作业,用来批量备份数据库,但是只能备份一次,第二次就报错,日志是:
已以用户 NT AUTHORITY\SYSTEM 的身份执行。 数据库中已存在名为 'Usp_BackUp_DataBase' 的对象。 [SQLSTATE 42S01] (错误 2714). 该步骤失败。
请高手指教,谢谢!
下面是脚本,请帮忙检查:
Use master
GO
/*=================Usp_BackUp_DataBase========================
=====BackUp Sigle DataBase ======
=====Ken.Guo ======
=====2010.9.10 ======
=====Version: 2005 & 2008 SQL Server ======
=====EXEC Usp_BackUp_DataBase 'MyDB','F:\Backup\MSsql' ======
============================================================
*/
CREATE PROC [dbo].[Usp_BackUp_DataBase] @DatabaseName nvarchar(200),@Path nvarchar(200)
AS
BEGIN
DECLARE @fn varchar(200)
,@sql varchar(1000)
SET @fn = @Path +(case when right(@Path,1) <>'\' then '\' else '' end)
+@DatabaseName+'_'
+convert(char(8),getdate(),112)+'_'
+replace(convert(char(8),getdate(),108),':','')
+'.bak'
set @sql = 'backup database '+@DatabaseName + ' to disk = N''' + @fn + ''''
--SELECT @sql
EXEC(@sql)
END
GO
Use master
GO
/*=============BackUp Mutile DataBase=========================*/
DECLARE @dbname nvarchar(200)
,@backup_path nvarchar(200)
SET @backup_path='F:\Backup\MSsql'
DECLARE db_info CURSOR
LOCAL
STATIC
READ_ONLY
FORWARD_ONLY
FOR
SELECT
name
FROM master.sys.databases WITH(NOLOCK)
WHERE
database_id>4
OPEN db_info
FETCH NEXT FROM db_info INTO @dbname
WHILE @@FETCH_STATUS=0
begin
EXEC master.dbo.Usp_BackUp_DataBase @dbname,@backup_path
FETCH NEXT FROM db_info INTO @dbname
END
close db_info
deallocate db_info
---------------------------------BackUp DataBase End------------------------
[解决办法]
我也正在这样一个批量备份数据库的存储过程,不用游标,参考:
create proc [dbo].[backup_db] @targetPath nvarchar(50)
as
set nocount on
if @targetPath=''
return ;
declare @sql nvarchar(max)
set @targetPath=@targetPath+case when RIGHT(@targetPath,1)='\' then '' else '\' end
set @sql=''
select @sql=@sql+'backup database ['+name+'] to disk='''+@targetPath+name+'.bak'' '+CHAR(10)
from sys.databases
where name not in('master','tempdb','model','msdb')
--print @sql
exec (@sql)
GO
Use master
GO
/*=================Usp_BackUp_DataBase========================
=====BackUp Sigle DataBase ======
=====Ken.Guo ======
=====2010.9.10 ======
=====Version: 2005 & 2008 SQL Server ======
=====EXEC Usp_BackUp_DataBase 'MyDB','F:\Backup\MSsql' ======
============================================================
*/
--如果已存在,那么就删存储过程
if exists(select * from sysobjects where name = 'Usp_BackUp_DataBase')
drop proc Usp_BackUp_DataBase
go
CREATE PROC [dbo].[Usp_BackUp_DataBase] @DatabaseName nvarchar(200),@Path nvarchar(200)
AS
BEGIN
DECLARE @fn varchar(200)
,@sql varchar(1000)
SET @fn = @Path +(case when right(@Path,1) <>'\' then '\' else '' end)
+@DatabaseName+'_'
+convert(char(8),getdate(),112)+'_'
+replace(convert(char(8),getdate(),108),':','')
+'.bak'
set @sql = 'backup database '+@DatabaseName + ' to disk = N''' + @fn + ''''
--SELECT @sql
EXEC(@sql)
END
GO
Use master
GO
/*=============BackUp Mutile DataBase=========================*/
DECLARE @dbname nvarchar(200)
,@backup_path nvarchar(200)
SET @backup_path='F:\Backup\MSsql'
DECLARE db_info CURSOR
LOCAL
STATIC
READ_ONLY
FORWARD_ONLY
FOR
SELECT
name
FROM master.sys.databases WITH(NOLOCK)
WHERE
database_id>4
OPEN db_info
FETCH NEXT FROM db_info INTO @dbname
WHILE @@FETCH_STATUS=0
begin
EXEC master.dbo.Usp_BackUp_DataBase @dbname,@backup_path
FETCH NEXT FROM db_info INTO @dbname
END
close db_info
deallocate db_info
---------------------------------BackUp DataBase End------------------------