Backup SMS Site Server Fails

[VictorySabre]

Hi,

I'm currently testing SMS 2003 for our company and have problems trying to get the SMS Site Maintenance Task, Backup SMS Site Server, to complete successfully.

It appears the task is able to backup the registry settings and files, but fails when it attempts to backup the database. When I look at the log file, it looks like it might be a permission problem, but I can't tell which account is having the problem.

My SMS test server is running in Advanced Security Mode. My MSSQLSERVER and SQLSERVERAGENT services are started successfully with a domain user account, that was configured through the SMS Enterprise Manager. I've tried adding the domain user account to the local Administrators group on the server also with no success.

Could someone please let me know what I am doing wrong or should try?

I've included a portion of the SMSBKUP.LOG:

Local backup location - trying direct database backup SMS_SITE_BACKUP 26/09/2004 6:02:39 PM 3784 (0x0EC8)
*** backup database SMS_ABC to DISK = 'E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat' with INIT SMS_SITE_BACKUP 26/09/2004 6:02:40 PM 3784 (0x0EC8)
*** [42000][3201][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat'. Device error or device off-line. See the SQL Server error log for more details. SMS_SITE_BACKUP 26/09/2004 6:02:40 PM 3784 (0x0EC8)
*** backup database SMS_ABC to DISK = 'E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat' with INIT SMS_SITE_BACKUP 26/09/2004 6:02:40 PM 3784 (0x0EC8)
*** [42000][3013][Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. SMS_SITE_BACKUP 26/09/2004 6:02:40 PM 3784 (0x0EC8)
Trying database backup through temp file SMS_SITE_BACKUP 26/09/2004 6:02:40 PM 3784 (0x0EC8)
*** backup database SMS_ABC to DISK = 'D:\_SM7A2.tmp' with INIT SMS_SITE_BACKUP 26/09/2004 6:02:41 PM 3784 (0x0EC8)
*** [42000][3201][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'D:\_SM7A2.tmp'. Device error or device off-line. See the SQL Server error log for more details. SMS_SITE_BACKUP 26/09/2004 6:02:41 PM 3784 (0x0EC8)
*** backup database SMS_ABC to DISK = 'D:\_SM7A2.tmp' with INIT SMS_SITE_BACKUP 26/09/2004 6:02:41 PM 3784 (0x0EC8)
*** [42000][3013][Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. SMS_SITE_BACKUP 26/09/2004 6:02:41 PM 3784 (0x0EC8)
Failed to backup site database SMS_ABC up to E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat: ~Error executing SQL backup command. SMS_SITE_BACKUP 26/09/2004 6:02:41 PM 3784 (0x0EC8)
STATMSG: ID=5033 SEV=E LEV=M SOURCE="SMS Server" COMP="SMS_SITE_BACKUP" SYS=SMSSERVER SITE=ABC PID=4508 TID=3784 GMTDATE=Mon Sep 27 01:02:41.274 2004 ISTR0="site" ISTR1="SMS_ABC" ISTR2="E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 SMS_SITE_BACKUP 26/09/2004 6:02:41 PM 3784 (0x0EC8)
Service SMS_SITE_COMPONENT_MANAGER on site system \\SMSSERVER successfully started. SMS_SITE_BACKUP 26/09/2004 6:02:43 PM 3784 (0x0EC8)
STATMSG: ID=5007 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_SITE_BACKUP" SYS=SMSSERVER SITE=ABC PID=4508 TID=3784 GMTDATE=Mon Sep 27 01:02:43.508 2004 ISTR0="SMS_SITE_COMPONENT_MANAGER" ISTR1="\\SMSSERVER" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 SMS_SITE_BACKUP 26/09/2004 6:02:43 PM 3784 (0x0EC8)
09/26/04 18:02:43 - backup completed. SMS_SITE_BACKUP 26/09/2004 6:02:43 PM 3784 (0x0EC8)

Many thanks in advance!

 

[coulters]

What do your SQL Server error logs say? Is SQL on the same box as SMS?

 

[VictorySabre]

Yes, SQL 2000 is installed on the same box as my test SMS 2003 server. I'm also backing up the database to a different drive (E within the same box.

I get the following in the Event Viewer at the times when the SMS backup job is set to run:
------------------
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 27/09/2004
Time: 6:02:43 PM
User: NT AUTHORITY\SYSTEM
Computer: SMSSERVER
Description:
Error: 15457, Severity: 0, State: 1
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

---------------------------
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 27/09/2004
Time: 6:02:45 PM
User: NT AUTHORITY\SYSTEM
Computer: SMSSERVER
Description:
18204 :
BackupDiskFile::CreateMedia: Backup device 'E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat' failed to create. Operating system error = 5(error not found).


For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

---------------------------
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 27/09/2004
Time: 6:02:45 PM
User: NT AUTHORITY\SYSTEM
Computer: SMSSERVER
Description:
3041 :
BACKUP failed to complete the command backup database SMS_ABC to DISK = 'E:\SMS_Backup\ABCBackup\SiteDBServer\SMSbkSQLDBsite.dat' with INIT


For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

-----------------------------------
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 27/09/2004
Time: 6:02:46 PM
User: NT AUTHORITY\SYSTEM
Computer: SMSSERVER
Description:
18204 :
BackupDiskFile::CreateMedia: Backup device 'D:\_SM822.tmp' failed to create. Operating system error = 5(error not found).


For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

-----------------------------------
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 27/09/2004
Time: 6:02:46 PM
User: NT AUTHORITY\SYSTEM
Computer: SMSSERVER
Description:
3041 :
BACKUP failed to complete the command backup database SMS_MDA to DISK = 'D:\_SM822.tmp' with INIT


For more information, see Help and Support Center at

http://go.microsoft.com/fwlink/events.asp.

------------------------------

Oddly, I cannot backup the transaction log for both the msdb and SMS_ABC databases...not sure if they are related.

 

[VictorySabre]

I poked around some more with adding the domain user account, used to start the SQL services, to the server's local administrator group and it appears to be backing up the database now.

It appears that when SMS performs the database backup, it erases my backup folder location and replaces the file/folder permissions with just the local Administrators group. Even if I added the domain user account to full control on that file/folder, whenever SMS ran the backup job, it would erase it all and replace it with just the local Administrators group.

Now, I thought starting up the SQL services with Local System or an account with local admin privileges is not recommended as listed in MBSA. I tried to build my server to best practices but it appears it breaks my server. Has anyone actually used a domain user account to start the SQL services without adding it to the local administrators group and still was able to backup the SMS Site Server successfully using the Site Maintenance tool?