Need urgent help !

Our backups have stopped working. The only change made was the server it
self was moved from one Domain to another. Our backup scripts do work
across our different domains. I've Googled and searched MS web site but have
not found anything that helps.
Here is the error that we are getting:
"Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
A cursor with the name 'ms_db' does not exist."
Here is the stored proc:
CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
null
AS
-- if the @.backup parameter is specified as 'backup', -
-- it will dump the dbs regardless of the @.sched_day
declare @.srvr_nm varchar(35),
@.cmptr_nm varchar(30),
@.rpt_loc varchar(50),
@.backup_loc varchar(50),
@.hist_rtntn int ,
@.hist_loc varchar(50),
@.sched_day int,
@.dmp_cd varchar(10),
@.drv_ltr char(1),
@.doit varchar(255),
@.dbname varchar(30),
@.backup_old varchar(50),
@.dw int,
@.dd int
--NEW ADDITION--
select @.dd = datepart(dd, getdate() )
select @.dw = datepart(dw, getdate() )
select @.srvr_nm = @.@.servername
exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
@.cmptr_nm output,
@.rpt_loc output,
@.backup_loc output,
@.hist_rtntn output,
@.hist_loc output,
@.sched_day output,
@.dmp_cd output
--@.@.sched_day represents the backup day of the week, 1 thru 7
-- 1 being sunday and 7 being saturday, 0 is dump that day
select @.drv_ltr = substring(@.rpt_loc,1,1)
select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
exec master..xp_cmdshell @.doit,no_output
if @.backup = 'backup' select @.sched_day = 0
select @.hist_loc = @.hist_loc+@.srvr_nm+''
select @.backup_loc = @.backup_loc+@.srvr_nm+''
select @.backup_old = @.backup_loc
if datepart(dd,getdate() ) between 1 and 7
and @.srvr_nm not in ('SXBOX23','WABOX3')
and @.hist_rtntn = 1 and @.sched_day <> 0
select @.backup_loc = @.hist_loc
if datepart(dd,getdate() ) = 1
and @.srvr_nm not in ('SXBOX23','WABOX23')
and @.hist_rtntn = 1 and @.sched_day = 0
select @.backup_loc = @.hist_loc
select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'del /q '+@.rpt_loc+'*.log'
exec master..xp_cmdshell @.doit,no_output
-- checks for the dbnm parameter and backups just the dbnm.
if @.dbnm is not null and @.backup is not null
begin
select @.dbname = @.dbnm
if @.srvr_nm in ("SXBOX23", "WABOX23")
and @.dw in (2,5) -- Mon or Thu
begin -- We are going to dump
select @.sched_day = 0
if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
select @.backup_loc = @.hist_loc
else
select @.backup_loc = @.backup_old
end
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -BkUpDB ' + @.backup_loc +
' -BkUpMedia DISK' +
' -BkUpOnlyIfClean' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
exec master..xp_sqlmaint @.doit
end
else
if @.dbnm is not null and @.backup is null
begin
select @.dbname = @.dbnm
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
exec master..xp_sqlmaint @.doit
end
else
if @.dbnm is null
begin
if @.dmp_cd = 'USERDB'
begin
declare ms_db cursor for
select name from master..sysdatabases
where dbid <> 2 order by dbid
for read only
end
if @.dmp_cd = 'SYSTEMDB'
begin
declare ms_db cursor for
select name from master..sysdatabases
where dbid < 5 and dbid <> 2 order by dbid
for read only
end
select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'del /q '+@.rpt_loc+'*.log'
exec master..xp_cmdshell @.doit,no_output
open ms_db
fetch next from ms_db into @.dbname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
if @.srvr_nm in ("SXBOX23", "WABOX23")
and @.dw in (2,5) -- Mon or Thu we are going to dump
begin
select @.sched_day = 0
if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
select @.backup_loc = @.hist_loc
else
select @.backup_loc = @.backup_old
end
if @.dw = @.sched_day or
@.sched_day = 0 /* daily backups */
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -BkUpDB ' + @.backup_loc +
' -BkUpMedia DISK' +
' -BkUpOnlyIfClean' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
else
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
exec master..xp_sqlmaint @.doit
END
fetch next from ms_db into @.dbname
END
close ms_db
deallocate ms_db
end
if @.dbnm is not null or @.backup is null
begin
select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
exec master..xp_cmdshell @.doit,no_output
end
select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
exec master..xp_cmdshell @.doit,no_output
select @.dbnm = @.dbname
select @.doit = 'echo off'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo --Dbcc and Dumps Report--[vbcol=seagreen]
>'+@.rpt_loc+'maintchk.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
'+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
\'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dump_
rpt.log
^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if errorlevel 1 findstr "Destination"
'+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit = @.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
if @.dw = @.sched_day or @.sched_day = 0
begin
select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
"MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
end
GOForgot to say that this is SQL2000 server with SP3a running on a Windows
2000 SP4 server.
Joe
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||I notice that if @.dmp_cd is neither USERCD nor SYSTEMCD, you fall through
with ms_db not declared which would give you this error. It might be worth
PRINT'ing @.dmp_cd to see what it is set to. Good coding practice would be
to abort the script and declare an error if it's not one of the values you
expect. No idea if this is your problem but if it's not now, it may be next
time.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||> I've Googled and searched MS web site but have not found anything that
> helps.
That's not surprising since this is not a SQL Server system procedure.

> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
Walking through your code, the reason for the immediate error is that
neither cursor was declared because @.dmp_cd is other than 'USERDB' or
'SYSTEMDB'. It looks like @.dmp_cd is assigned via the following snippet:
select @.srvr_nm = @.@.servername
exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
@.cmptr_nm output,
@.rpt_loc output,
@.backup_loc output,
@.hist_rtntn output,
@.hist_loc output,
@.sched_day output,
@.dmp_cd output
I suggest you check to ensure @.@.SERVERNAME returns the expect name and, if
so, that sp_qg_get_backup_info is returning a valid @.dmp_cd value.
BTW, you should never name user stored procedures with a 'sp_' prefix.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||I can't believe what I am reading.
You wrote scripts, not to automate backups, but to run SQL maintenance plans
with programmatically determined parameters?! Most likely there is a
permissions issue somewhere that broke this specific script. However, the
underlying problem is executing the maintenance utility directly. I would,
and have, written scripts that invoke the specific backup and file
maintenance tasks directly for each database. I even wrote a "master"
script that creates the jobs for a new database. I find that far more
maintainable than a strange, kludgey script that invokes an even stranger
and kludgier (albiet a Microsoft approved kludge) maintenance utility.
I like the SQL Maintenance plans for simple systems and beginning DBAs.
Anyone who can write a script this complex should be able to do the same
using T-SQL backup commands. And the SQL Agent has a fairly nifty and
reliable scheduler that does have a supported t-sql interface. This is a
major example of re-inventing the wheel, only to come up with a lopsided
octagon.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||I have to ask, "Why!" but not because the script is failing but rather why
did you write it in the first place?!
First off, sp_qg_dump_db causes a scan of the master database. (amatuerish).
Second, to do a backup in a script takes about 1 line for a full backup of
either the log or data or 1 line for an incremental.
Try thinking "small code is good code" and it will usuall work nowhere you
put it.
d.
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
have
> not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to
dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
>
\'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dump_
rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>