October 7, 2008 at 4:12 am
Sorry for the length of this topic, but i wanted to include everything.........
This problem occuring on a Windows 2003 Server, SQL 2000 SP3
I have a nightly maintenance plan which is scheduled to run on all our SQL Servers (2000 and 2005) at 8pm each night. It obtains a list of the databases on the server, determines weather the server is using Litespeed or Native backups and performs the following tasks:
1. If not in simple mode - EXEC('BACKUP LOG ' + @DB + ' WITH NO_LOG')
2. If Litespeed Backup - /* Perform LiteSpeed Backup */
BEGIN
EXEC ('master.dbo.xp_backup_database
@database = ' + @DB + ' ,
@filename = ''' + @FULL_PATH + ''' ,
@init = 1 ,
@threads = ' + @THREADS + ' ,
@servername = ' + @sqlserver + '' )
/* Initialise Native Log Backup */
EXEC ('master.dbo.xp_backup_log
@database = ' + @DB + ' ,
@filename = ''' + @LOGPATH + ''' ,
@init = 1 ,
@threads = ' + @THREADS + ' ,
@servername = ' + @sqlserver + '' )
3. If Native Backup - /* Perform Native Backup */
BEGIN
SELECT 'STARTING TO BACKUP DATABASE : ' + CONVERT(CHAR(20),GETDATE(),113)
EXEC ('BACKUP DATABASE ' + @DB + ' TO DISK = ''' + @FULL_PATH + '''WITH INIT')
/* Initialise Native Log Backup */
IF @RECOV_MODE <> 'SIMPLE'
EXEC('BACKUP LOG ' + @DB + ' TO DISK = ''' + @LOGPATH + ''' WITH INIT')
Print 'Log File Initialised For ' + @DB
select 'Time Now: ' + convert(varchar(20), getdate())
END
4. backup directory is then copied to a DR server using Robocopy job.
One of the databases is 20GB and there is an identical copy of this database used for reporting. Both of these databases will back up correctly, and other nights the main database (not the reporting database) will not back up. It will be the only one that fails with the following error:
Msg 3201, Level 16, State 1, Server SQLTVWPRD1, Line 1
Cannot open backup device 'G:\SQLBACKUPS\Timeview_Full_Native.BKP'.
Device error or device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server SQLTVWPRD1, Line 1
BACKUP DATABASE is terminating abnormally.
Log File Initialised For Timeview
SQL Server error logs the following:
BackupDiskFile::CreateMedia: Backup device 'G:\SQLBACKUPS\Timeview_Full_Native.BKP' failed to create. Operating system error = 32(error not found).
After some googling, i found the following Microsoft issue
http://support.microsoft.com/kb/905616
which advises running the follwing statement which worked:
BACKUP DATABASE DatabaseName TO DISK 'C:\.........' WITH FORMAT
However, I am getting the same issue again and only with this database?? I am at my wits end..... any help much appreciated!!!
Jim
P.S I have realised this server is running on SQL2000 Personal Edition, problem???
October 7, 2008 at 4:26 am
Is it possible that something could be using the backup file? An anti-virus, a copy?
Why are you doing a backup log with no log before the full backup? How often are your log backups running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 5:06 am
-- Is it possible that something could be using the backup file? An anti-virus, a copy?
I suppose it is possible something could be running such as anti-virus software? I' will check this with my Server Team.
-- Why are you doing a backup log with no log before the full backup? How often are your log backups running?
Log backups run from 8am to 7.30pm.
backup log with no log is something that was always done by the DBA prior to me coming in to this environment, but it might be that this script is adapted from some very big databases.
BOOKS ONLINE Quote
"Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE."
So should we be backing up the databases first, then running a NO_LOG to truncate the log??
Does this mean that all committed transactions are removed from the log, hence saving space? Sorry, i'm a little confused as to what this command is actually doing?
Jim
October 7, 2008 at 5:24 am
Backup.. with no log shouldn't be run at all.
You're discarding log entries and breaking the recovery chain. Practically what it means is that if the latest full backup fails and you need to restore, you'll only be able to apply the log backups up to the point of the backup ... with nolog.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply