SQL7, SP3:
I backup the databases (Master, model, msdb, db1, db2) to disk every night on our test system. I then use Veritas Backup exec to backup the os files to tape. I'm using backup devices, I've recreated them several times to try and resolve. Theres' enough disk space as when I run a bu in Query analyser it's fine. But throught EM there's a problem:
I've been receiving an error in the EM job:
------------------------------------
Write on 'db1devicename' failed, status = 112. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3202) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
------------------------------------
Then in the SQL log:
------------------------------------
BackupDiskFile::OpenMedia: Backup device 'E:dbaBackupdb1Full.bak' failed to open. Operating system error = 2(The system cannot find the file specified.).
I confirmed that the device is pointing to thecorrect path, there is space, the db activity is low or no users at the time.
This happens only on one server. Fine on three others with similar setup.
Hi, I have Sqlserver 7.0 cluster configured with Omniback 3.5. My backups were fine till yesterday.We moved the database to another node and bacups are failing with error in sql server error log. backupVirtualDeviceset::Intialize:open failure on backup device 'Omniback_II_msdb' operating system error -2147024894(The system cannot find file specified) I greatly appreciate if any one can help to solve this thanks Mohan
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
I have the standard version of SQL2005 and have a backup maintenance plan that runs every Sunday consisting of the following steps.
1. Check Database Integrity
2. Rebuild Index
3. Back Up Database (Full)
The backup itself works fine, but sometimes during the backup I am unable to insert new records into the database that I am backing up.
I believe from all the documentation and other comments I have read that the database should always be available for inserts or updates even during the backup operation. The error I receive from VB.NET application is:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
The problem only occurs during a full backup, not differentials or tranaction log backups. Sometimes after catching the above error I can insert into another table, but at other times I am not able to insert into the desired or backup table and simply have to write the data to a file and insert it manually at a later time.
This problem does not consistently occur since a backup may take 10 minutes and during that period maybe 5 of 6 records are inserted correctly, but one will fail.
Why doesn't the database always allow inserts during the backup? It should, shouldn't it?
I have schedule backup job from SQL Server 2000 to Tape. Each Jobs consist of 4 steps which backup different database.
Sometimes I encounter problem. The Message is
Executed as user:dbo. The file on device '\.Tape0' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
There is no problem at the command, as it will run normally when i manually trigger the schedule job. Sometimes the error occur only after few days the job run.
Hello, I have MS SQL 2005 server with 300+ databases on it. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, it can create 3-4 databases a day (random time). I've scheduled full backup of all databases to run once at night, and it runs just fine. Besides that, I have scheduled tran logs backup of all databases to run every hour. This backup fails from time to time with the following error:
Executing the query "BACKUP LOG [survey_p0886464_test] TO DISK = N'D:\backups\log backups\survey_p0886464_test_backup_200708072300.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0886464_test_backup_20070807230002', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.
I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure?
Or is it possible to schedule full backup to run if tran log backup fails? Any advice will be much appreciated.
i have a backup of sql server 2000 pro sp4 database and i want to import it to a sql server 2005 developer edition sp2. i tried to import the database using RESTORE DATABASE IntEx FROM DISK = 'C:projectsackupBDIntEx.bak' and got the following error
quote: Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataIntEX_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). Msg 3156, Level 16, State 3, Line 1 File 'IntEX_Data' cannot be restored to 'C:Program FilesMicrosoft SQL ServerMSSQLdataIntEX_Data.MDF'. Use WITH MOVE to identify a valid location for the file. Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLdataIntEX_Log.LDF" failed with the operating system error 3(The system cannot find the path specified.). Msg 3156, Level 16, State 3, Line 1 File 'IntEX_Log' cannot be restored to 'C:Program FilesMicrosoft SQL ServerMSSQLdataIntEX_Log.LDF'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
i tried then importing it with the restore function from sql manager and got the error:
quote: TITLE: Microsoft SQL Server Management Studio ------------------------------
Restore failed for Server 'AMDINSIDETESTBED'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLIntEx.MDF'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
as i said i'm using a sql server 2005 sp2. i tried the scripted method when i did not have sp2 in sql server and it worked without a problem. after installing sp2 it seems it does not want to work anymore. i also tied making a backup of a sql server 2005 database, deleting the database and restoring it and it worked again.
I am trying to make my maintenance plan to backup on a remote server but it fails with:
"failed with the following error: "xp_delete_file() returned error 2".
I am pretty sure its because it doesnt have full access to the share folder... My question is, how can I grant access to it, if I am using a local account as sql agent service: NT ServiceSQLSERVERAGENT.I need to change the service account?
A full database backup file was created and placed in my C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLBackup folder. In attempting to restore the file using "Restore Database", I get the following error: System.Data.SqlClient.SqlError: Directory lookup for the file "d:Microsoft SQL ServerMSSQLdataworkspace.mdf" failed with the operating system error 3 (The system could not find the file path specified.).
It's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM LogJob History (Backup_AssetData_2009-08-08)
Step ID3 ServerSQL01 Job NameBackup_AssetData_2009-08-08 Step NameBackup Database - Second Attempt Duration00:05:33 Sql Severity16 Sql Message ID3013 Operator Emailed Operator Net sent Operator Paged Retries Attempted0
Message Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData( iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = ' iscsrv-dcm04 iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1
I have 2 different servers that run nightly backup jobs to a network drive using the UNC format. I am noticing the larger databases encountering the following errors on a regular basis
Error: 18210, Severity: 16, State: 1. '\fnfssql3SQLBackupsMNSQL05<database>.BAK'. Operating system error 64(The specified network name is no longer available.). BACKUP failed to complete the command BACKUP DATABASE <database>. Check the backup application log for detailed messages.
There are 5 databases on this server all backed up by the same job and 3 of them wortk fine and 2 of them fail so it is not a permissions problem. Have there been any problems with backing up to a network drive in 2005?
We currently have a production server (prodserver) and backup server (backupserver) in the same domain, and I am attempting to backup dbs from the production box on to the backup box using the following:
backup database db to disk = '\backupservere$ackupdb.bak' with init
when executing from SSMS, I receive the following error:
Msg 3201, Level 16, State 1, Line 1 Cannot open backup device '\backupservere$ackupdb.bak' Operating system error 1326 (Logon failure: unknown user name or bad password)
Here's where the issue lies for me:
Both the production and backup box are running services under the same domain accounts, let's call it "domainsqlagent"
domainsqlagent has domain admin rights on backupserver and prodserver
I can login to both the production and backup servers using the domain account, map drives, hit administrative shares, drag and drop files, etc between the servers
If I use xp_cmdshell to try and simply move a file between prodserver and backupserver, I receive the above error as well. However, if I open an cmd window and execute the move command from DOS, it will successfully move.
There is a development server on the domain as well (devserver) using domainsqlagent to run the service accounts, and I can successfully backup databases from dev to prod, dev to backup, backup to dev, backup to prod, prod to dev, but NOT prod to backup Finally, if I create a maintenance plan in SSMS, I cannot create a connection to the backup server (error reads the connection may not be configured correctly or you may not have the right permissions on this connection)
The backup server was changed to a different domain (domain2) before I arrived here, but has been switched back now, rebooted, service accounts changed via Configuration Manager, services restarted (and rebooted again for good measure). Does SSMS use a different security context to create the connections to remote servers? From all my tests, the domainsqlagent account has all the correct rights and is working OK. Could this be a network issue? The network admin and I have been banging our heads against a wall for the past couple of days, so if anyone has seen something like this, I'd appreciate any help.
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
Hi,I keep getting this error message for a trans.log backup.Operating system error112(error not found).The disk has about 6GB space free, and the backup should only take upabout 550 MB, so I would think it is not space related but...The disk is NTFS.Any ideas?
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files. Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database? I can then attach the .mdf files if I need to restore the database to another server. Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin] GO IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL DROP PROC usp_DeleteBackup GO
I want to know how people are backup up their win2k system and SQL 2000 server. If you want a run backup once a night and first backup win2k file system and then run backup using sql agent will that work? Thanks.
SQL 7.0 SP1. We do SQL Server backup and also third part backup (with SQL agent active) of SQL Server databases. Sometimes happen this situation:
01.00 SQL Server backup of DB1 01.00 third part backup of DB1 02.00 SQL Server T-LOG backup of DB1 03.00 SQL Server T-LOG backup of DB1 03.30 SQL Server differnatial DB backup of DB1 04.00 SQL Server T-LOG backup of DB1
or this situation:
01.00 SQL Server backup of DB1 02.00 SQL Server T-LOG backup of DB1 03.00 SQL Server T-LOG backup of DB1 03.10 third part backup of DB1 03.30 SQL Server differnatial DB backup of DB1 04.00 SQL Server T-LOG backup of DB1
My questions now are: Are my SQL Server backups valid (DB,Differenatial and T-LOG)? In case of a restore operation, can I use my SQL Server backups without problem? In other words, can we leave the third part backup tool active or it should be taken away?
Can anybody clarify this for me? Thank you in advance. Franco
when i try to make backup in location different sql server express 's default location for making backup device it tackes an error
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Backup failed for Server 'PDSTUDIOSQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
System.Data.SqlClient.SqlError: Cannot open backup device 'SQLEXPRESS2005(D:My DocumentsVisual Studio 2005DataBasesBackupSQLEXPRESS2005.bak)'. Operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------ And this is my code for making backup
USE master GO
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'SQLEXPRESS2005', @physicalname = N'D:My DocumentsVisual Studio 2005DataBasesBackupSQLEXPRESS2005.bak' GO Server : SQL Server 2005 Express Edition
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Usually get this kind of message in the task log file:
"BACKUP LOG prod_cust TO pc_0930_tran WITH INIT" Processed 173 pages for database 'prod_cust', file 'pcprod_log' on file 1. BACKUP LOG successfully processed 173 pages in 1.438 seconds
While I got the following message this morning.
"BACKUP LOG prod_cust TO pc_0930_tran WITH INIT" There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. Processed 93 pages for database 'prod_cust', file 'pcprod_log' on file 1. BACKUP LOG successfully processed 93 pages in 1.367 seconds (0.553 MB/sec).
Just restored my dev server due to a hardware failure. Replaced motherboard, hds, and cpu. Restored from backup but now my nightly backup fails on one database.
It gives error: BackupMedium::ReportIoError: write failure on backup device 'G:MY.BAK'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).
It successfully does a 3G but then fails on the 13G db. There is only 256Mb of RAM that used to be 384. Could this be the problem? I'm going to put in another stick of 128 tonight but could it be something else?
In my DB Backup JOB my first step is to map a network path like this NET USE Z:\SERVER2DADOS /user somebody 234567
I got this Error --> Executed as user: MYSERVERSYSTEM. System error 1312 has occurred. A specified logon session does not exist. It may already have been terminated. Process Exit Code 2. The step failed.
In step 2 i copy all my *.bak files from yesterday to the drive mapped on dir BACKUP in SERVER2.
COPY "C:BACKUP*.BAK" "Z:BACKUP"
I got this Error --> Executed as user: MYSERVERSYSTEM. ...annot find the path specified. C:BACKUPdb_200511030200.BAK The system cannot find the path specified. C:BACKUPCFG_db_200511030225.BAK The system cannot find the path specified. C:BACKUPSTRUCTAL_db_200511030225.BAK The system cannot find the path specified. C:BACKUPSTRUCTEN_db_200511030225.BAK The system cannot find the path specified.
step 3 i delete all files in MYSERVER C:BACKUP*.BAK
This step is OK
step 4 i execute my database maintenance plan
the .bak file are generated and put in "C:BACKUPS" in MYSERVER
I am getting an error msg: BackupMedium::ReportIoError : write failure on backup device 'D:MSSQL$DBSQL4AL thats all I can see. Now thi is been running for a few weeks with no problem. Untill yesterday.
Database: SQL2000 OS: Windows 2000 SP4 Backup device: local hard disk (E Drive)
When I backup the database I got this error: BackupMedium::ReportIoError: write failure on backup device 'E:Program FilesMicrosoft SQL ServerMSSQLBACKUP5WavesDB'. Operating system error 112(There is not enough space on the disk.).
My backup file only needs 4G and my E Drive has 13G free. My log files has been set to "growth automatically". Does anyone help to give some advise on this. Thank you for any help.
Here is my situation, I am backing up two databases on a periodic basis, however one database gets backed up without any issues, the other database gives me the following error message. I have done quite a lot of research, but have not found a solution to this problem. Here is the error thats being generated -
"BACKUP failed to complete the command BACKUP LOG [R1MaxCRM] TO DISK = N'D:SQL BackupsR1MaxCRMR1MaxCRM_tlog_200709120000.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT"
