March 2, 2007 at 1:11 pm
We have installed SQL Server 2005 SP2 on a test server, so we can work out our procedures prior to upgrading our production systems. One of our DBAs created a maintenance plan to backup a specific user database. He then created a second user database and changed the maintenance plan to backup all user databases. The backup is not finding the second database. When we check the log of the backup job, it only shows the first database and not the second:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3033
Report was generated on "Servername".
Maintenance Plan: User Databases
Duration: 00:02:58
Status: Succeeded.
Details:
Back Up Database (Full) (Servername)
Backup Database on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Full
Append existing
Task start: 2007-03-01T08:58:51.
Task end: 2007-03-01T09:01:48.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\databasename''
GO
BACKUP DATABASE [database] TO DISK = N''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\databasename\databasename_backup_200703010858.bak'' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N''databasename_backup_20070301085851'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''databasename'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''databasename'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''databasename'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Current\databasename\databasename_backup_200703010858.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
Has anyone else seen this?
March 3, 2007 at 12:35 am
After your posted I have tested....it is working fine...
Check if any one of the db compatibility is 70 or 80?
MohammedU
Microsoft SQL Server MVP
March 3, 2007 at 12:36 am
After your posted I have tested....it is working fine...
Check if any one of the db compatibility is 70 or 80?
MohammedU
Microsoft SQL Server MVP
March 5, 2007 at 9:42 am
I did check using sp_cmptlevel. The all databases are level 80.
March 6, 2007 at 1:41 pm
I don't know it is a BUG or not...but
Try after applying sp2 hotfix which will be available either today or tomorrow...
MohammedU
Microsoft SQL Server MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply