August 12, 2008 at 8:09 pm
We're using SQL 2005 SP2, build 3233, on Windows Server 2003 SP2.
We have a SQL job that reads sysdatabases and backs up each DB to a fileshare.
It runs at 10pm nightly on the servers in our Dev domain.
DECLARE @db varchar(100), @bkdevice varchar(200)
DECLARE DBs CURSOR FOR
select name from sysdatabases
where name not in ('tempdb') order by name
OPEN DBs
FETCH NEXT FROM DBs INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @bkdevice = '\\Dev-FILES\sqlbackups$\QA-Stuff\' + @db + '_db.bak'
BACKUP DATABASE @db TO DISK=@bkdevice WITH INIT, format
FETCH NEXT FROM DBs INTO @db
END
CLOSE DBs
DEALLOCATE DBs
Some nights it does not back up all DBs. Here's an example from one server of which DBs were backed up each evening:
8/6: ABCDB, master
8/7: ABCDB
8/8: ABCDB, master
8/9: ABCDB
8/10: ABCDB, master, model, msdb, ReportServer, ReportServerTempDB
8/11: ABCDB
There are several servers in the Dev domain, and most show this behavior, with no obvious consistency. The SQL Server logs show no errors; they just show ABCDB backed up but not others, for example.
In our production domain, no servers show this behavior.
The main difference (I think) may be with the service account that SQL runs under. It's a Windows domain account called ServiceSQL. On Dev domain servers, Dev\ServiceSQL is not a member of local Administrators on the server. On the production domain servers, Prod\ServiceSQL is a member of local Administrators. (We will be revamping our security so the service account is different on each server and is not a local Admin.)
Has anyone seen the like before? I'm speculating that it's a combination of a race condition and a security issue. The race condition may be that while the SELECT queries sysdatabases, it locks something (or something else is locking it), so that the SELECT does not return all DBs. But sometimes it does return all DBs. I notice that if it skips, it usually (but not always) starts at or before master DB. It may skip later DBs, alphabetically. (Yes, the logs are backed up too, in a subsequent job step.)
The security issue may be that when SQLService runs the SELECT, it doesn't have enough authority to override the lock when it's not an Admin (or otherwise doesn't have enough authority to get the whole list)?
As I said, there are no error messages, like "Can't back up the DB". On one server, I told the job step to output its log to a table, and it shows no errors either. No errors in the Windows Event Log. Are there traces I could turn on to dig more deeply?
Sorry if these are newbie questions or I'm using SQL Server terminology incorrectly. I was an IBM DBA, now doing MS SQL Server, and still getting up to speed on all this. Thanks in advance for any help.
August 12, 2008 at 11:11 pm
I've encountered a similar issue with sysdatabases, where running 2 consecutive queries against that catalog view yielded different results, although the state of the databases has not changed. You could try persisting the names in a temporary table, and work from there e.g.
SELECT name INTO #dblist FROM sys.databases
WHERE NAME NOT IN ('tempdb') ORDER BY NAME
DECLARE @db varchar(100), @bkdevice varchar(200)
DECLARE DBs CURSOR FOR
SELECT name FROM #dblist ORDER BY NAME
OPEN DBs
FETCH NEXT FROM DBs INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @bkdevice = '\\Dev-FILES\sqlbackups$\QA-Stuff\' + @db + '_db.bak'
BACKUP DATABASE @db TO DISK = @bkdevice WITH INIT, FORMAT
FETCH NEXT FROM DBs INTO @db
END
CLOSE DBs
DEALLOCATE DBs
DROP TABLE #dblist
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 13, 2008 at 11:10 am
Very strange. I think that Ray Mond's advice is good. Another thing could be to add some logging to the script and output it to a sql server agent output file. This could help you to find out what happens.
I have a stored procedure that you could use for backing up your databases that you're welcome to use.
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
August 13, 2008 at 11:53 am
I just worked through a similar problem like this, you may have looked at some of this, if you have ignore this post.
1. Look through your application logs on the Server, Event Viewer, for errors.
2. Is disk compression running on the file share, if so this can cause a partial lock on some of your databases and stop the backups from running.
3. Do you have a tape backup job running around the same time that your SQL server backups are scheduled to run, if so you may be fighting IO contention.
Hope this helps
August 14, 2008 at 7:34 pm
Thank you all for your suggestions. I think I'll try Ray's suggestion first.
Ola, I'm not sure what logging to add or how. Do you mean something like PRINT @NAME? It is already going to an output file, which shows no errors.
Todd, those are good things to look at.
1. No errors in any system event logs.
2. No compression on the target fileshare.
3. Pretty sure there are no tape backups at that time, and it can happen when I manually kick it off during the day. I've seen tape contention in our production environment, usually because the SQL backup throws an error of one sort or another.
August 15, 2008 at 6:28 am
I was just thinking that you could do a
select name from sys.databases
in the start of the script.
If you use a temporary table you could do a
select name from #dblist
right after you've populated the temporary table.
It's just some debugging code.
Ola Hallengren
August 19, 2008 at 1:25 pm
Ray's suggestion appears to work fine. I just wish I understood why.
August 28, 2008 at 7:26 pm
Why not just use the built in maintenance plan with SQL 2005 to do the backups....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply