Backup SQL Server Failures

I get the following errors when I use a maintenance plan to backup all of my databases on one of our servers. Note: When I run them manually they work as long as I put them in a different place on the same drive. The Everone group has full access on the NTFS drive that this is writing to so it can't be a rights issue. Also some of my databases are backed up while others fail. The account that I'm using to back these up is an administrator of the machine, I'm using the same account when I automate them through a maintenance plan that I do for doing them manually. The E:\ drive (where these are trying to be backed up) is Raid 5 with 30 gb free and 30gb used. These backups would only take up about 5gb of space (if they would work).

Please help!!!

BACKUP failed to complete the command BACKUP LOG [WebLogs] TO DISK = N'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731. TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Internal I/O request 0x2E0CB728: Op: Write, pBuffer: 0x17D20000, Size: 983040, Position: 328286720, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x13914200, OffsetHigh: 0x0, m_buf: 0x17D20000, m_len: 983040, m_actualBytes: 0, m_errcode: 2, BackupFile: E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.TR N

BackupMedium::ReportIoError: write failure on backup device 'E:\SQLBackups\WebLogs\WebLogs_tlog_200304180731.T RN'. Operating system error 2(The system cannot find the file specified.).Try to redefine the Maint.plan and also try using BACKUP LOG statement individually for this database.|||Thanks for your reply.

I've redefined my Maintenance Plan several times, as you could imagine, but to no avail.

Also, I've ran the BACKUP LOG statement on all of my databases in the place of the maintenance plan and received the same error.|||What is the SP[service pack] level on the SQL Server & OS?|||Windows 2000 Server SP3

SQL Server 2000 SP3 (Standard Edition not Enterprise)|||Then how about RECOVERY MODEL on these DBs.|||All of the user databases on this server have the Recovery Model option set to FULL.|||Looks like issues with NTFS permissions, check whether the SQL Services have necessary privileges and also under REGISTRY keys for these accounts.|||Since this is an internal machine and not our Internet SQL Server, the SQL Server Services (Main, Agent, etc.) all run under an account that is an administrator of the machine.

Security rights for both logical drives on the machine have the Everyone group having all rights locally. (The network shares only allow administrators in)|||As you'd mentioned the disk is 60GB, whereas 30Gb is free and another 30 is used.

Could you point out exact size of databases and free space available on the disk. It looks like when the Maint.plan is running typically the error refers to Free space on the server.

For a test try to redesing maint.plan for each database and see the results.|||The drive has 21.0 GB used. It has 38.7 GB free.

There are 16 databases to be backed up. The error happens randomly upon the 3 largest databases.

Email database = 263.88 mb (data and log)
ESP database = 316.25 mb (data and log)
WebLogs database = 5,069.76 gb (data and log)

(note 5 datafiles in the WebLogs and one log file... low amount of transactions and the database and the log is shrunk periodically throughout the day. We backup and truncate the log after a main import process is executed each day.)

I've ran the backup by hand using BACKUP DATABASE and it seldom works on these databases. Sometimes it will actually complete, others it doesn't. Also, the WebLogs database never completes and it always seems to fail at 24 percent (stats = 1 on the backup database command).

The backup log doesn't work at all on these databases whether I run it by hand or through the maintenance plan.|||Sorry for wasting your time.

I finally became fed up and went throught the security check points and actually went to the physical machine and looked in the event viewer of windows.

There I found that one of the Raid disks was going bad. That explains a lot.

Thanks for all of your help.

You had a lot of great ideas. If I had the ability to "dial in" to the machine, I would have spotted the problem much earlier.

I'm betting you agree that a bad disk could cause all of the quirky backup errors?|||Dont' be sorry.... Glad to hear your resolution and efforts, keep it up.
No second thought for these failures, but anytime if you've them again then follow the listings above.

Good luck.

Backup query ?

Im currently planning to move a Sql Server Database from one Server to another due to SAN errors on the current server.

What I need to know is...

Im currently restoring from last nights backup.. I then plan to apply all the transaction logs from today and tomorrow.
(Up until downtime)

If the database back's up between now and tomorrow will I loose data at the points between the back and the next transaction log backup ??

Im not truncating the transaction log at backup but I just wanted to make sure that ive got this straight before I start the work.

Thanks.You will not loose data between the full backup and the next transactional log backup. That is, of course, everything was done correctly.

Backup of databases within SQL Server 2005 Express

Hi there!

Is there any replacement for the missing SQL Server Agent which could be configured to backup "all databases" and fix minor errors in databases, rebuild indexes, etc.?

Or let's better ask: is there any solution/tool out there to create and execute maintenance plans without the need of SQL Server Agent? (It might be a simple tool that enumerates the DBs and executes some T-SQL statements on them)

Thanks
JochenManagement Studio Express provides UI to do those things, but there is no Agent in SQL Server Express, so the tasks can only be executed once in the UI.

Instead, you could use the UI to create the T-SQL, script it to a file rather than execute it, and then use the Windows scheduler to launch sqlcmd.exe to execute the script every day.|||This goes into the right direction. With that I could create the backups manually. But it is very time consuming to setup this for many SQL servers and many databases. Especially, when a new database will be created, it's important to not forget to create a new task for the additional database.

Is there another, more elegant way or better a tool to do the job?|||I would recommend using the auto-close feature in your connection string. Basically, when all the users disconnect the database file will automatically detach from the sql server, leaving it free to access by regular backup programs. I really like this feature!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp|||Automating Database maintenance in SQL 2005 Express Edition Part I

Automating Database maintenance in SQL 2005 Express Edition Part II

Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.

Backup multiple database wiht msde in a sql script problem

HI,
I'm new to sql and i would like to run a script to make a backup of msde database, i have modified this script but i'm getting some errors running it. Could any first tell me if my @.command syntaxe is correct if not what would be a way of setting a schedu
led job to run a backup of multiple database in one job.
Here are the errors i'm getting
C:\Documents and Settings\ncsadmin>osql -U sa -i c:\smslab3.sql -n
Password:
Msg 119, Level 15, State 1, Server SMSLAB3, Line 7
Must pass parameter number 5 and subsequent parameters as '@.name = value'.
After the form '@.name = value' has been used, all subsequent parameters must be
passed in the form '@.name = value'.
Msg 119, Level 15, State 1, Server SMSLAB3, Line 7
Must pass parameter number 5 and subsequent parameters as '@.name = value'.
After the form '@.name = value' has been used, all subsequent parameters must be
passed in the form '@.name = value'.
Msg 156, Level 15, State 1, Server SMSLAB3, Line 10
Incorrect syntax near the keyword 'EXEC'.
Thanks
--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.
-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @.job_name = 'MSDEBACKUPJOB',
@.enabled = 1,
@.description = 'MSDEBACKUPJOB',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'raould.traore@.mcgill.ca'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE EDMSO TO DISK = ''c:\MSDESQLBACKUP\EDMSO.dat_bak''','BACKUP DATABASE master TO DISK = ''c:\MSDESQLBACKUP\master.dat_bak''','BACKUP DATABASE model TO DISK = ''c:\MSDESQLBACKUP\model.dat_bak''','BACKUP DATABASE msdb TO DISK = ''c
:\MSDESQLBACKUP\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG EDMSO TO DISK = ''c:\MSDESQLBACKUP\EDMSO.log_bak''','BACKUP LOG master TO DISK = ''c:\MSDESQLBACKUP\master.log_bak''','BACKUP LOG model TO DISK = ''c:\MSDESQLBACKUP\model.log_bak''','BACKUP LOG msdb TO DISK = ''c:\MSDESQLBACKUP\msdb
.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'MSDEBACKUPJOB', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'MSDEBACKUPJOB',
@.name = 'ScheduledBackup_msdb',
@.freq_type = 4, --daily
@.freq_interval = 1, --once
@.active_start_time = '153000' --(10:30 am) 24hr HHMMSS.
Ralph,
I have modified your script slightly (below) and it should now work. There
was a problem with the number of single quotes which meant that the command
wasn't enclosed in a string. This applied to both commands.
Regards,
Paul Ibison
EXEC sp_add_job @.job_name = 'MSDEBACKUPJOB',
@.enabled = 1,
@.description = 'MSDEBACKUPJOB',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'raould.traore@.mcgill.ca'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Data',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE EDMSO TO DISK =
''c:\MSDESQLBACKUP\EDMSO.dat_bak'',BACKUP DATABASE master TO DISK =
''c:\MSDESQLBACKUP\master.dat_bak'',BACKUP DATABASE model TO DISK =
''c:\MSDESQLBACKUP\model.dat_bak'',BACKUP DATABASE msdb TO DISK =
''c:\MSDESQLBACKUP\msdb.dat_bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'MSDEBACKUPJOB',
@.step_name = 'Backup msdb Log',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG EDMSO TO DISK =
''c:\MSDESQLBACKUP\EDMSO.log_bak'',BACKUP LOG master TO DISK =
''c:\MSDESQLBACKUP\master.log_bak'',BACKUP LOG model TO DISK =
''c:\MSDESQLBACKUP\model.log_bak'',BACKUP LOG msdb TO DISK =
''c:\MSDESQLBACKUP\msdb.log_bak''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'MSDEBACKUPJOB', @.server_name = N'(local)'
-- Run job. Starts the job immediately.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'MSDEBACKUPJOB',
@.name = 'ScheduledBackup_msdb',
@.freq_type = 4, --daily
@.freq_interval = 1, --once
@.active_start_time = '153000' --(10:30 am) 24hr HHMMSS.

backup job quits without any errors

Hi

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'C:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

|||Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @.@.fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.

|||

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.

backup job quits without any errors

Hi

I have a job which is executed via SQL agent -it attempts to backup each database..

The only problem is that it terminates about 1/2 through (not always at the same spot) - there are about 80 databases on the server

No errors are logged.. Where do I look next ?

The commands executed are as follows:

--

DECLARE @.DB_Name varchar(32)
DECLARE @.Backup_Path varchar(255)
DECLARE @.Backup_Name varchar(255)

DECLARE DB_Cursor CURSOR FOR SELECT NAME FROM sysdatabases

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor INTO @.DB_Name

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.DB_Name <> 'tempdb' AND @.DB_Name <> 'model'
BEGIN
print '--<< ' + @.db_name + ' >>--'
SET @.Backup_Path = N'C:\sql2005backups\nightly\' + @.DB_Name + 'Daily' + '.bak'
SET @.Backup_Name = @.DB_Name + N' backup'
BACKUP DATABASE @.DB_Name TO DISK = @.Backup_Path WITH INIT
END
FETCH NEXT FROM DB_Cursor INTO @.DB_Name
END

CLOSE DB_cursor
DEALLOCATE DB_cursor

print 'Finished backing up'

Have you tried running the TSQL code from within a management studio or a query analyzer window to see where it fails? Does it run to completion in from the Query Analyzer/Management Studio window?

The other thing that you can do is if you go into the properties of the SQL Server Agent job you can have it put the results of the commands to a text file.

In the SQL Server Management Studio or 2000 Enterprise Manager, go to the properties of the job. Edit the step and click the advanced tab. In the middle of the page there will be a text box called Output File. Just browse to where you would like the output of the commands to reside and indicate a file name.

You will be able to see what errors the SQL Server Agent job is encountering.

Drew

|||Are databases being added or removed (detatched/attached) during the backup processing maybe?

If the script quits without errors, does it write your completion message? @.@.fetch_status is a tri-state value. Either a -1 or a -2 value (any value but 0) would end your loop.

You may want to include more robust checking on that value.

In SQL Server 2005 you could throw a select from sys.dm_exec_cursors at the end of your script to see what the ending status was on the cursor down there with the completion message.|||

thanks - I'll check it out your suggestions

no - no databases are being being detached etc and yes, it does get to the completion message..

Bruce.