Network Backups SQL server 2005 Ent

I am trying to backup to a network drive. When I choose backup to disk it
will not show anything but my local drives. I put in a UNC or a mapped
drive and it says it cannot validate the existance of the backup directory.
My server has access to the shared drives on the other machines why can't
SQL server see it.
Is there something I have to turn on in SQL Server 2005 before it will let
me backup over the network?
Any idea's on why this is not working for me?
Hi
The Service Account that SQL Server runs under needs to be a domain account,
and must have permissions on the destination share. Same as for SQL Server
2000
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Unreal400" <Unreal400@.discussions.microsoft.com> wrote in message
news:E4BD6092-C082-4063-AFE8-3FAA866E8C75@.microsoft.com...
>I am trying to backup to a network drive. When I choose backup to disk it
> will not show anything but my local drives. I put in a UNC or a mapped
> drive and it says it cannot validate the existance of the backup
> directory.
> My server has access to the shared drives on the other machines why can't
> SQL server see it.
> Is there something I have to turn on in SQL Server 2005 before it will let
> me backup over the network?
> Any idea's on why this is not working for me?

Network Backups SQL server 2005 Ent

I am trying to backup to a network drive. When I choose backup to disk it
will not show anything but my local drives. I put in a UNC or a mapped
drive and it says it cannot validate the existance of the backup directory.
My server has access to the shared drives on the other machines why can't
SQL server see it.
Is there something I have to turn on in SQL Server 2005 before it will let
me backup over the network?
Any idea's on why this is not working for me?Hi
The Service Account that SQL Server runs under needs to be a domain account,
and must have permissions on the destination share. Same as for SQL Server
2000
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Unreal400" <Unreal400@.discussions.microsoft.com> wrote in message
news:E4BD6092-C082-4063-AFE8-3FAA866E8C75@.microsoft.com...
>I am trying to backup to a network drive. When I choose backup to disk it
> will not show anything but my local drives. I put in a UNC or a mapped
> drive and it says it cannot validate the existance of the backup
> directory.
> My server has access to the shared drives on the other machines why can't
> SQL server see it.
> Is there something I have to turn on in SQL Server 2005 before it will let
> me backup over the network?
> Any idea's on why this is not working for me?

network backups fail - error 64

I've found a lot of people with this same problem, but not really any
solutions. We have scheduled backup jobs that run & backup DBs over
the network to a UNC path on our SAN/NAS. A few nights ago, most of
the larger database backups started failing pretty regularly with the
following errors present in the logs.
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
network name is no longer available.)
BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK =
'\\UNCPath\msdb_dump.BAK', WITH INIT, Format
BackupMedium::ReportIoError: write failure on backup device
'\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
network name is no longer available.).
Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
\\UNCPath\DB_dump.BAK
I don't know of a good solution here - can anyone help? I don't have
access to the Event Logs on the systems, but I'm trying to get to them,
and I'll repost if I find anything.
Hi,
The OS error 64 usually indicates the intermittent network connectivity
problems.
To ensure that the backups for the database are taken properly, I recommend
you backup to
the local hard disk drive and then create a OS scheduled job to move the
backup file
to the share upon completion of the SQL Backup.
In parallel Inform your network infrastructure team to investigate this
issu. As well as run the below command from command prompt during backup to
check the network issue.
ping <ipaddress_of_remote_machine> -t >pingtest.txt
Once the problem occur verify the pingtest.txt for any Request time Out.
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1121174032.571685.322230@.o13g2000cwo.googlegr oups.com...
> I've found a lot of people with this same problem, but not really any
> solutions. We have scheduled backup jobs that run & backup DBs over
> the network to a UNC path on our SAN/NAS. A few nights ago, most of
> the larger database backups started failing pretty regularly with the
> following errors present in the logs.
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.)
> BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK =
> '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
> BackupMedium::ReportIoError: write failure on backup device
> '\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.).
> Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
> 983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
> 0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
> 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\UNCPath\DB_dump.BAK
> I don't know of a good solution here - can anyone help? I don't have
> access to the Event Logs on the systems, but I'm trying to get to them,
> and I'll repost if I find anything.
>
|||How large is your DB?
Do you have any problem when you copy large files, more than 1GB, to the
SAN/NAS?
<unc27932@.yahoo.com> wrote in message
news:1121174032.571685.322230@.o13g2000cwo.googlegr oups.com...
> I've found a lot of people with this same problem, but not really any
> solutions. We have scheduled backup jobs that run & backup DBs over
> the network to a UNC path on our SAN/NAS. A few nights ago, most of
> the larger database backups started failing pretty regularly with the
> following errors present in the logs.
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.)
> BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK =
> '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
> BackupMedium::ReportIoError: write failure on backup device
> '\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.).
> Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
> 983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
> 0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
> 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\UNCPath\DB_dump.BAK
> I don't know of a good solution here - can anyone help? I don't have
> access to the Event Logs on the systems, but I'm trying to get to them,
> and I'll repost if I find anything.
>
|||The failing DBs range from 100mb to 8-10gb. I can copy from my client
to the SAN via a mapped network drive to the same share that the DB is
pointing to, just in windows, no problem (it takes forever for the 8gb
file, but it works).
|||Another thing - the backup appears to run OK - using STATS I can view
it progressing but right before it's done it blows up...
A good one says this:
96 percent backed up.
97 percent backed up.
98 percent backed up.
99 percent backed up.
Processed 37680 pages for database 'DB', file 'DB_dat' on file 1.
100 percent backed up.
Processed 1 pages for database 'DB', file 'DB_log' on file 1.
BACKUP DATABASE successfully processed 37681 pages in 22.736 seconds
(13.576 MB/sec).
My bad one says this:
96 percent backed up.
97 percent backed up.
98 percent backed up.
99 percent backed up.
Processed 37680 pages for database 'DB', file 'DB_dat' on file 1.
100 percent backed up.
Processed 1 pages for database 'DB', file 'DB_log' on file 1.
<Then it says something like error 3013, BACKUP DATABASE is terminating
abnormally>

network backups fail - error 64

I've found a lot of people with this same problem, but not really any
solutions. We have scheduled backup jobs that run & backup DBs over
the network to a UNC path on our SAN/NAS. A few nights ago, most of
the larger database backups started failing pretty regularly with the
following errors present in the logs.
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
network name is no longer available.)
BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK = '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
BackupMedium::ReportIoError: write failure on backup device
'\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
network name is no longer available.).
Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
\\UNCPath\DB_dump.BAK
I don't know of a good solution here - can anyone help? I don't have
access to the Event Logs on the systems, but I'm trying to get to them,
and I'll repost if I find anything.Hi,
The OS error 64 usually indicates the intermittent network connectivity
problems.
To ensure that the backups for the database are taken properly, I recommend
you backup to
the local hard disk drive and then create a OS scheduled job to move the
backup file
to the share upon completion of the SQL Backup.
In parallel Inform your network infrastructure team to investigate this
issu. As well as run the below command from command prompt during backup to
check the network issue.
ping <ipaddress_of_remote_machine> -t >pingtest.txt
Once the problem occur verify the pingtest.txt for any Request time Out.
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1121174032.571685.322230@.o13g2000cwo.googlegroups.com...
> I've found a lot of people with this same problem, but not really any
> solutions. We have scheduled backup jobs that run & backup DBs over
> the network to a UNC path on our SAN/NAS. A few nights ago, most of
> the larger database backups started failing pretty regularly with the
> following errors present in the logs.
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.)
> BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK => '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
> BackupMedium::ReportIoError: write failure on backup device
> '\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.).
> Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
> 983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
> 0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
> 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\UNCPath\DB_dump.BAK
> I don't know of a good solution here - can anyone help? I don't have
> access to the Event Logs on the systems, but I'm trying to get to them,
> and I'll repost if I find anything.
>|||How large is your DB?
Do you have any problem when you copy large files, more than 1GB, to the
SAN/NAS?
<unc27932@.yahoo.com> wrote in message
news:1121174032.571685.322230@.o13g2000cwo.googlegroups.com...
> I've found a lot of people with this same problem, but not really any
> solutions. We have scheduled backup jobs that run & backup DBs over
> the network to a UNC path on our SAN/NAS. A few nights ago, most of
> the larger database backups started failing pretty regularly with the
> following errors present in the logs.
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.)
> BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK => '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
> BackupMedium::ReportIoError: write failure on backup device
> '\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.).
> Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
> 983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
> 0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
> 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\UNCPath\DB_dump.BAK
> I don't know of a good solution here - can anyone help? I don't have
> access to the Event Logs on the systems, but I'm trying to get to them,
> and I'll repost if I find anything.
>|||The failing DBs range from 100mb to 8-10gb. I can copy from my client
to the SAN via a mapped network drive to the same share that the DB is
pointing to, just in windows, no problem (it takes forever for the 8gb
file, but it works).|||Another thing - the backup appears to run OK - using STATS I can view
it progressing but right before it's done it blows up...
A good one says this:
96 percent backed up.
97 percent backed up.
98 percent backed up.
99 percent backed up.
Processed 37680 pages for database 'DB', file 'DB_dat' on file 1.
100 percent backed up.
Processed 1 pages for database 'DB', file 'DB_log' on file 1.
BACKUP DATABASE successfully processed 37681 pages in 22.736 seconds
(13.576 MB/sec).
My bad one says this:
96 percent backed up.
97 percent backed up.
98 percent backed up.
99 percent backed up.
Processed 37680 pages for database 'DB', file 'DB_dat' on file 1.
100 percent backed up.
Processed 1 pages for database 'DB', file 'DB_log' on file 1.
<Then it says something like error 3013, BACKUP DATABASE is terminating
abnormally>sql

network backups fail - error 64

I've found a lot of people with this same problem, but not really any
solutions. We have scheduled backup jobs that run & backup DBs over
the network to a UNC path on our SAN/NAS. A few nights ago, most of
the larger database backups started failing pretty regularly with the
following errors present in the logs.
BackupDiskFile::RequestDurableMedia: failure on backup device
'\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
network name is no longer available.)
BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK =
'\\UNCPath\msdb_dump.BAK', WITH INIT, Format
BackupMedium::ReportIoError: write failure on backup device
'\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
network name is no longer available.).
Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
\\UNCPath\DB_dump.BAK
I don't know of a good solution here - can anyone help? I don't have
access to the Event Logs on the systems, but I'm trying to get to them,
and I'll repost if I find anything.Hi,
The OS error 64 usually indicates the intermittent network connectivity
problems.
To ensure that the backups for the database are taken properly, I recommend
you backup to
the local hard disk drive and then create a OS scheduled job to move the
backup file
to the share upon completion of the SQL Backup.
In parallel Inform your network infrastructure team to investigate this
issu. As well as run the below command from command prompt during backup to
check the network issue.
ping <ipaddress_of_remote_machine> -t >pingtest.txt
Once the problem occur verify the pingtest.txt for any Request time Out.
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1121174032.571685.322230@.o13g2000cwo.googlegroups.com...
> I've found a lot of people with this same problem, but not really any
> solutions. We have scheduled backup jobs that run & backup DBs over
> the network to a UNC path on our SAN/NAS. A few nights ago, most of
> the larger database backups started failing pretty regularly with the
> following errors present in the logs.
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.)
> BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK =
> '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
> BackupMedium::ReportIoError: write failure on backup device
> '\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.).
> Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
> 983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
> 0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
> 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\UNCPath\DB_dump.BAK
> I don't know of a good solution here - can anyone help? I don't have
> access to the Event Logs on the systems, but I'm trying to get to them,
> and I'll repost if I find anything.
>|||How large is your DB?
Do you have any problem when you copy large files, more than 1GB, to the
SAN/NAS?
<unc27932@.yahoo.com> wrote in message
news:1121174032.571685.322230@.o13g2000cwo.googlegroups.com...
> I've found a lot of people with this same problem, but not really any
> solutions. We have scheduled backup jobs that run & backup DBs over
> the network to a UNC path on our SAN/NAS. A few nights ago, most of
> the larger database backups started failing pretty regularly with the
> following errors present in the logs.
> BackupDiskFile::RequestDurableMedia: failure on backup device
> '\\UNCpath\msdb_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.)
> BACKUP failed to complete the command BACKUP DATABASE msdb TO DISK =
> '\\UNCPath\msdb_dump.BAK', WITH INIT, Format
> BackupMedium::ReportIoError: write failure on backup device
> '\\UNCpath\DB_dump.BAK'. Operating system error 64(The specified
> network name is no longer available.).
> Internal I/O request 0x26485108: Op: Write, pBuffer: 0x28690000, Size:
> 983040, Position: 3191806464, UMS: Internal: 0x0, InternalHigh:
> 0xF0000, Offset: 0xBE3F1A00, OffsetHigh: 0x0, m_buf: 0x28690000, m_len:
> 983040, m_actualBytes: 0, m_errcode: 64, BackupFile:
> \\UNCPath\DB_dump.BAK
> I don't know of a good solution here - can anyone help? I don't have
> access to the Event Logs on the systems, but I'm trying to get to them,
> and I'll repost if I find anything.
>|||The failing DBs range from 100mb to 8-10gb. I can copy from my client
to the SAN via a mapped network drive to the same share that the DB is
pointing to, just in windows, no problem (it takes forever for the 8gb
file, but it works).|||Another thing - the backup appears to run OK - using STATS I can view
it progressing but right before it's done it blows up...
A good one says this:
96 percent backed up.
97 percent backed up.
98 percent backed up.
99 percent backed up.
Processed 37680 pages for database 'DB', file 'DB_dat' on file 1.
100 percent backed up.
Processed 1 pages for database 'DB', file 'DB_log' on file 1.
BACKUP DATABASE successfully processed 37681 pages in 22.736 seconds
(13.576 MB/sec).
My bad one says this:
96 percent backed up.
97 percent backed up.
98 percent backed up.
99 percent backed up.
Processed 37680 pages for database 'DB', file 'DB_dat' on file 1.
100 percent backed up.
Processed 1 pages for database 'DB', file 'DB_log' on file 1.
<Then it says something like error 3013, BACKUP DATABASE is terminating
abnormally>

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)+'[vbcol=seagreen]
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--
>'+@.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+'$\'+substring(@.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
GO
Forgot 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+'$\'+substring(@.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
>
>
|||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+'$\'+substring(@.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
>
>
|||> 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+'$\'+substring(@.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
>
>
|||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+'$\'+substring(@.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
>
>
|||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+'$\'+substring(@.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
>
>

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
>
>