Guys I was wondering if you can help me figure out the problem with the script.
1) I am getting the following error message on one of the job which is backing up all the databases on the server.
...ges for database 'AdminWorkFlow_Reports', file 'AdminWorkFlow_Reports_Data' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AdminWorkFlow_Reports',
file 'AdminWorkFlow_Reports_Log' on file 1. [SQLSTATE 01000] (Message 4035) Backup or
restore operation successfully processed 433177 pages in 64.328 seconds (55.163 MB/sec).
[SQLSTATE 01000] (Message 3014) Processed 113720 pages for database 'BMSRep', file 'BMS_Data'
on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'BMSRep', file '
BMS_Log' on file 1. [SQLSTATE 01000] (Message 4035) Backup or restore operation
successfully processed 113721 pages in 17.200 seconds (54.162 MB/sec). [SQLSTATE 01000]
(Message 3014) Processed 208 pages for database 'EmailAddress', file 'EMailAddress_STGE_Data'
on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'EmailAddress',
file 'EMailAddress_STGE_Log' on file 1. [SQLSTATE 01000] (Message 4035) Backup or restore
operation succe... The step failed.
Not sure where to start. I looked up the backup script it looks fine.
BackUp Database AdminWorkFlow_Reports to Disk = 'q:\AdminWorkFlow_Reports12-17-2007.bak'
--
--
--
--
BackUp Database EmailAddress to Disk = 'q:\EmailAddress12-17-2007.bak'
2)Also I have another script which for some reason doesn't run on some nights. Its enabled and the job is scheduled daily but its not running for about a week.
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
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'd:sql2005backupsightly' + @DB_Name + 'Daily' + '.bak' SET @Backup_Name = @DB_Name + N' backup' BACKUP DATABASE @DB_Name TO DISK = @Backup_Path WITH INIT print '' END FETCH NEXT FROM DB_Cursor INTO @DB_Name END
I have a trans log backup that runs every 15 minutes on san, works fine until I do a backup after a large load. I get the below error message. Anyone had this before?
Executed as user: DPSCSDOMsqlexec. The file on device 'n:sqllogsmdentallgdmp' is not a valid Microsoft Tape Format backup set. [SQLSTATE 42000] (Error 3242) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step fail
My transaction log backup is failing with the following error: Does anyone know how I can fix it? (null) Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194 Copyright (C) Microsoft Corporation (null) Logged on to SQL Server as 'NT AUTHORITYSYSTEM' (trusted) Starting maintenance plan 'Maint Plan - TLogs' on 5/7/2001 8:18:11 AM Backup can not be performed on database. This sub task is ignored. (null) Thanks
I am trying to backup a database to a tape. It works when it wants to. I get a 3013 error. The drive is fully functional. Seagate backup works fine. SQL Backup does not. I think it might be that the tape needs retensioning sometimes. Here is the sql statement: BACKUP DATABASE pubs TO TAPE='.ape0' WITH FORMAT. Is there a retension parameter I am failing to see?
Our Transaction Log backup job runs every two hours from 7:00am to 8:59pm. Today, our Full backup job and the first Transaction Lob backup job ran successfully. Subsequent Transaction Log Backups have failed with the following error message under Job History:
"The job failed. The Job was invoked by Schedule 13 (Schedule 1). The last step to run was step 1 (Step 1)."
The caveats are that the database is in Full recovery mode and a .TRN file is generated on the server. Also, the SQL Server log says the job ran successfully. Any ideas?
HelloI'm seeing the backup using SMO fails with big databases (>15GB). Ihave a 2GB database and I can see the command go thru. in Profiler butafter it's about 50-70% done, I get an exception saying backup failed.There is no details on the SQLServer log.Here's the code snippet:bk.Initialize = m_backupInit;bk.PercentCompleteNotification = 10;bk.PercentComplete += newPercentCompleteEventHandler(bk_PercentComplete);bk.SqlBackup(m_Server);Here's the command as captured by SQL-Profiler:BACKUP DATABASE [Test_MODEL] TO DISK = N'D:TempTest_Model_Backup.dat' WITH NOFORMAT, INIT, NOSKIP, REWIND,NOUNLOAD, STATS = 10Has anyone else seen this. The wierd thing is it works using old SQL-DMO.thanksSunit
I've problems with backing up the certificate (on the Witness Server). On principal and mirror, it works fine (Win 2003 Server, US English), but on my local PC (XP Prof, German) what i use as witness Server following backup command raises an error:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'something$Strong123';
CREATE CERTIFICATE HOST_W_cert WITH SUBJECT = 'HOST_W certificate for database mirroring', START_DATE = '01/01/2006', EXPIRY_DATE = '12/31/2050';
BACKUP CERTIFICATE HOST_W_cert TO FILE = 'C:HOST_W_cert.cer';
The error message is:
Msg 15240, Level 16, State 1, Line 1
Cannot write into file 'C:HOST_W_cert.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
I connect to the database with a domain user what is a local admin on the witness server (my local user), so this couldn't be a permission reason. The file doesn't already exists, so that is not the problem.
I have creted a backup device on network drive and getting the following error when I run the backup command as a job.SQL server and the agent are running on a domain account which has full permission on the network drive.Any help is greatly appreciated. Thanks!
"Cannot open backup device 'mccdiffbkpond2build'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed"
Okay. I changed the times that the transaction logs are backed up, via the built in maintenance schedule. It was then that I started to get failures only on the Transaction log backup for the master. The error that I get from the history log is Backup can not be performed on this database. This sub task is ignored. If I look in the file that is saved to disk :
Starting maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:00 Backup can not be performed on database 'master'. This sub task is ignored.
End of maintenance plan 'DB Maintenance Plan1' on 09/11/2004 02:30:31 SQLMAINT.EXE Process Exit Code: 1 (Failed)
I changed the backup back to its original time as this was the only change made. This has not resolved the problem. As I am new to SQL and still finding my feet All the other SQL maintenance plans that I changed are working fine.
If I try to backup of database in sql 2012 with t-Sql as follows:
Backup database db1 to disk='c:myfullbkup.bak' with init go
This code works absolutely fine.
But if I try to take backup through SSMS -Task -Backup in object explorer and gave the same path of c: drive or any other folder outside sql default "Backup" folder.It gives error as access denied.This was not the scene in Sql server 2008.
I am running a SQL Server 2000 installation with several databases. Eachdatabase and log is backed-up using a maintenance plan.The scheduled maintance plan for the latest database does not run, butdisplays no error. There is no entry in the job history. The same thinghappens when I try to run the individual jobs from Enterprise Manager.I've checked the database recovery model (full), the location of the backupfiles (same as the other databases), and just about everything else I canthink of. The scheduled maintenance plan for every other database runs asit should.What am I missing?ThanksIain
I'm running SQL 2005 SP2 and having a problem with a Database Maintenance task created in SSMS doing transaction log backups of all user databases.
When I look at dbo.sysmaintplan_logdetail I see the message:
Database 'Database1' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Great - that's what we want! Skip the SIMPLE recovery mode databases. However, the next entry is the following error message:
Executing the query "BACKUP LOG [Database1] TO DISK = N'D:\MSSQL$Windows\MSSQL.2\MSSQL\Backup\Database1\Database1_backup_200707231600.trn' WITH NOFORMAT, NOINIT, NAME = N'Database1_backup_20070723160023', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. 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.
Which says to me it is trying to backup the same database(s) it just said it was skipping!
What's going on? I see that there is a fix to a similar error message with the cumulative update KB936305 (http://support.microsoft.com/kb/936305) for maintenance cleanup tasks.
I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.
I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..
select bmf.physical_device_name, RIGHT(bmf.physical_device_name, CHARINDEX('', REVERSE(bmf.physical_device_name))-1) as physical_device_file, bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.type, bs.first_lsn, bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn into #backup from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf where bmf.media_set_id = bs.media_set_id and bs.backup_finish_date is not null AND bs.type = 'D' AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name) order by bs.database_name, bs.backup_start_date asc
DECLARE GetBackup CURSOR FOR select database_name, backup_finish_date from #backup order by database_name
OPEN GetBackup FETCH NEXT FROM GetBackup INTO @dbname, @Status
WHILE @@FETCH_STATUS = 0 BEGIN select @message = @message + @dbname + ' backup up on ' + @Status + Char(13) FETCH NEXT FROM GetBackup INTO @dbname, @Status END Close GetBackup Deallocate GetBackup
I am using SQL Server 2000 for one of our web application system called eRoom. eRoom uses SQL Server 2000 as a database. Now I have used SQL Server 2000 Database backup features to run the 4 database backup every night at 10pm. The backup runs fine. But recently I have found that for some reason when Our server machine which is hosting eRoom and SQL Server (Windows Server 2003 Standard Edition) is restarted, the database backup does not run anymore.
Now I have checked SQL Server settings and the settings allow SQL Server and SQL Server agent to restart both the services when the operating system starts. But still the database backup does not run when the server machine is restarted. It happened twice last week. Can anyone please help me?
Here are some information to help you understand the problem.
Backup Process Followed:
I went to SQL Server 2000 Enterprise Manager, then went to the appropriate server and then right click the Database>All Tasks> Backup Database> and then selected the backup file which is a (.BAK) file and then scheduled the database to be backed up at 10:00pm every night. I followed the same procedure for all 4 databases.
I am running SQL Server 2000 Standard Edition on Windows Server 2003 Standard Edition R2
Hello, SQL 2000 backup to C dirve fails with event 17055 Operating system error = 5 access is denied. Creating and running a backup job wizards errors with SQL State 42000, Deveice error or device off line. SQL is running as LocalSystem. The backup destination has every Windows group added with Full Control. Thanks
Hi everyone, I am running SQL 2000 SP3. I have one maintenence plan that is backing up one database and that is working fine. I am trying to create another maintenence plan for another database but the job does not execute and it gives me errors. Both SQL server and the agent uses 'local system account' to run the service. I have tried changing this to a domain account to see if it fixes anything. Also on the jobs, the job owner is a domain account. Below are two erros I am getting when running the jobs. Error1: BackupDiskFile::CreateMedia: Backup device 'D:SQL Backups....mybackupfile.bak' failed to create. Operating system error = 3(The system cannot find the path specified.).
Error2: BACKUP failed to complete the command BACKUP DATABASE [Database_Name] TO DISK = N'D:SQL Backups....mybackupfile.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
I have a maintenance plan step to perform transaction log backups. There are a few databases that are offline. The backup seems to fail because it tries to backup the offline databases. The backup step is by itself as one of three subplans, although it also fails when it is the only step in a plan with only one subplan.
I have another server that is only on SP1 and it does not have problems handling the offline databases.
The database is SQL Server 2000. Recover Plan = 'Simple'. Maintenance Plan always fails when DBCC tries to put database in single-user mode so it can do the backup, if there is a user has a connection to it from EssBase. No queries running - just a connection. How can I get SQL Server to automatically kill the connections so it can do the backup?
This is my first posting and actually I have big problem which I need to resolve immediately:
I have a 38 GB Database on my SQL Server which I want to full-backup. Until now backup was created on the same volume where the database resides. But now the volume is out of disk space. So what we did is that we connected an external USB disk drive with 500 GB to the SQL Server and try to backup there.
But our first and all consecutive backup attemps failed with error SQL-DMO (ODBC SQLState: 42000). It's working fine the first few minutes as we can see that the backup file is continously growing. But then it abruptly aborts with above error message.
Can anyone figure out why ? Any help is appreciated.
I am experiencing an issue with SQL Server 2000 Maintenance Plan. DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS). Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
Event Type: Error Event Source: MSSQL$SHAREPOINT Event Category: (2) Event ID: 17055 User: NT AUTHORITYSYSTEM Description: 18210 : BackupVirtualDeviceFile:repareToFreeze: failure on backup device '{74A09E78-8845-47EB-9602-705E4406096C}1'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
Event Type: Error Event Source: VSS Event Category: None Event ID: 6013 User: N/A Description: Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft OLE DB Provider for SQL Server Error message: BACKUP DATABASE is terminating abnormally.
Event Type: Error Event Source: MSSQL$SHAREPOINT Event Category: (2) Event ID: 17055 User: NT AUTHORITYSYSTEM Description: 18210 : BackupVirtualDeviceFile:repareToFreeze: failure on backup device '{74A09E78-8845-47EB-9602-705E4406096C}5'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).
I need to get the Backup System working again urgently. Any advice will be greatly appreciated
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
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?
We are trying to install the Standard Edition of SQL Server 2005. We already have SQL Express installed. Standard won't install and Express won't uninstall.
We have tried every suggestion we can find and some we made up. When we try to install Standard, we get to the bit where you choose what you want to install and the only thing available is workstation components.
There are so many cases of this on the Internat that I can't believe MS have not solved the issue. And I am stunned that they want to charge us more money to talk to us about how to fix something we just paid them an arm and a leg for!
What do I need to tell you to get some idea of where to go next with this?
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?
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.
