SP2 Maintenance Backup Fails On Offline Database.
May 29, 2007
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.
Is this an issue with SP2(9.0.3054)?
View 4 Replies
ADVERTISEMENT
Jul 23, 2007
Hi,
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.
Does anybody else have such a problem?
View 1 Replies
View Related
Apr 5, 2007
Hi All,
There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.
I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.
Any pointers ?
View 3 Replies
View Related
Feb 13, 2004
Hi all,
I have an application running on internet, it uses SQL server datatase.
I have an question want to as: can i backup database and update database online or offline?
Thanks.
TungNT
View 1 Replies
View Related
Jul 16, 2014
I'm using 2014 SE.I know a backup of a database doesn't take the source DB offline, but then I need to move this DB to another server (for intensive reporting work). At present we restore the DB, but that means putting the DB in single user mode, kicking everyone off, and completing the restore.
I see from 2014 EE notes that "online restore" is possible. EE is of course, mightily expensive.Or perhaps it's possible to configure things to speed up the restore process somehow, so there is less downtime?, the resource impact in creating the backup is quite high, perhaps there's a way to (apart from playing with backup compression) reduce the impact on the source server here?
View 8 Replies
View Related
Sep 21, 2006
I €˜d like to discus with you the following REAL enough disaster scenario:
1. The TEST database is in the FULL backup mode.
2. WE have a full TEST DB backup and all tr. log backups.
3. DBA moved TEST database OFFLINE for maintenance operations.
4. MDF file for TEST DB was removed by mistake.
5. TRANSACTION LOG (LDF) file is OK.
6. DBA want to recover database to the point of failure.
According with MS SQL Server 2000 documentation it is possible.
We need to backup the transaction log , BUT I CANNOT DO THIS.
PLEASE, HELP.
In the same time, I can make LDF backup and recover database to the point of failure, if database is online and I stop/start SQL Server to remove MDF file.
View 4 Replies
View Related
Apr 21, 2003
I have several Maintenance jobs that keep failing. They
are , Integrity Checks, Index rebuild, and Update
statistics. I keep getting a similar error on all three. I
have pasted it below. None of my other SQL 2000 servers
have any problems. I have tried changing the database
options in question. The error I keep getting is as
follows:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'DAPHNE' as 'CDCNorm' (trusted)
Starting maintenance plan 'FSPRD84A Integrity Checks' on
4/21/2003 10:20:59 AM
[1] Database FSPRD84A: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.
** Execution Time: 0 hrs, 0 mins, 8 secs **
End of maintenance plan 'FSPRD84A Integrity Checks' on
4/21/2003 10:21:07 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Any suggestions?
View 8 Replies
View Related
Oct 18, 2012
We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error:  "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture.  To alter or drop the index, you must first disable Change Data Capture on the table.  The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.Â
View 4 Replies
View Related
Apr 10, 2007
Hi, I am relative newbie to SQLServer. When I try to take a user database offline, the query "hangs," with the query processing circle spinning. The Sharepoint 7 application is running on top of the Enterprise SQL Server 2005 db with several logins sleeping and awaiting commands. No errors are generated until I kill the offline command. Anyone have any ideas? Do I need to kill all the connections?
View 2 Replies
View Related
Apr 18, 2008
Created a maintenance plan to backup my sharepoint databases.
When I execute it the following error occurs:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI)
- Execute maintenance plan. SharePointBackUP (Error)
Messages
* Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
------------------------------
ADDITIONAL INFORMATION:
Job 'SharePointBackUP.Backup_SharePoint' failed. (SqlManagerUI)
When checking the Maintenance PlansharePointBackUP log it is empty!
Under Job History I thinks this:
Date 4/18/2008 12:55:35 PM
Log Job History (SharePointBackUP.Backup_SharePoint)
Step ID 1
Server DESD7
Job Name SharePointBackUP.Backup_SharePoint
Step Name Backup_SharePoint
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error.
Under SQL Agent there are no entries!
So what bloody log am I suppose to be checking?! This is very frustrating.
I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
View 5 Replies
View Related
Aug 14, 2007
Hello
We created maintenance plans for Backup, we configured as:
1. Backup set expires after 2 days. (but we still see backup files are at the location from day one)
2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.
please advice. Thanks, Jay
View 1 Replies
View Related
Aug 21, 2014
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.
View 8 Replies
View Related
Aug 28, 2006
Hello,
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
select @message = @message + char(13) + Char(13) + 'Backup Status' + Char(13)
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
drop table #backup
print @message
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of sqlserver!',
@type = N'text/html',
@message = @message
Thanks
View 1 Replies
View Related
Jan 8, 2008
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
Any help would be appreciated.
Thanks.
View 1 Replies
View Related
Dec 3, 2001
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?
View 2 Replies
View Related
Dec 2, 2004
ok, here is an interesting one.
We have multiple jobs that run the following SQL Backups to Permanent Backup devices.
Backup Database DBX to DBXFull with NoInit - 1 am
Backup Database DBX to DBXDiff with Differential, NoInit - 12 pm
Backup Log DBX to DBXLog with NoInit - every hour on the hour
the devices are in path E:SQLBackups<databasename>
E: is on a SAN
Every Night E: is backed up by legato, When Legato Starts to backup the directory, at the hour a transaction log backup occurs. The Backup Fails and The Database Is Taken OFFLINE.
Questions are as follows.
What is causing the db to be taken offlline?
How can i stop the db from being taken offline while still mainting my current backup strategy.
Temporarily we have stopped tlog backups during the directory backup phase (legato) and then resume them when it is complete.
Difficulty Level: we will not be using Legato's SQL Plugin for the backups, they have to be performed with jobs and SQL Backup.
View 14 Replies
View Related
Jul 23, 2005
Hi there,Used the wizard to create a maintenance plan for a db. But it fails most ofthe time. I checked the log-files.At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It succeedsalways.At 1.05 AM, for 1 second, it runs "integrity check", but most of the time itfails, and says that it couldn't switch to single-user because other usersare using the database.And when "integrity check" fails the "backup" job won't run at 1.10 AM. Whenit DOES run it takes 2 seconds.How do I find out who is using the database ? How do I put a sp_who (orsimilar) in the "integrity check" job ?I got this tip:----------Assuming you have SQL2000, then you can use something like this tokick out all the users:ALTER DATABASE foo SET RESTRICTED_USER WITH ROLLBACK IMMEDIATEIf you want to know who is using the database, then this is one way:select suser_sname(sid)from master..sysprocesseswhere dbid = db_id('foo')---------But how do I actually do it ? How do I edit a maintenance plan ?thx/jim
View 5 Replies
View Related
Nov 28, 2000
I am running an overnight job for table Maintenance - this job execs a SP. It has been working okay for a while and is suddenly failing (last two weeks) with Error 515 cannot insert nulls into column. I realise that it is possible that a change has been made but cannot for the life of me ascertain what it is. I have checked server config, database properties and user options and it all appears to be the same as a server that is running this job okay. Please help!!
View 1 Replies
View Related
Feb 12, 2004
I created a maintenance plan to back up a database. It fails and I get the follow error in the log file.
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
What user is it referring to? What user does a Maintenance plan use to login as? SA on the server has full access to all databases. SA’s default database is master and I can read and write to it with the enterprise manager and query analyzer.
All replies appreciated
View 2 Replies
View Related
Mar 31, 2008
Hello,
On SQL 2005 with mixed authentication, I am simply trying to create a maintenance plan to backup one single database. When I get to the end of the wizard, the creation process fails with this message:
Create maintenance plan failed().
I installed SQL 2005 using my domain account and the services are running under my account credentials. I am a domain and local admin.
Any ideas?
Regards,
Chris
View 5 Replies
View Related
Feb 25, 2008
I created a user db integrity maintenance task. The created job runs once a week. It fails. How do you tell at what point is fails? The history log is not specific about where things go off the rails.
The job errors as follows -
Execution of job "User Integrity check subplan_1 failed. See history log for details
If I run the sql in a query window it goes right through - error free.
USE [AcAspNetDB]
GO
DBCC CHECKDB(N'AcAspNetDB', NOINDEX)
GO
USE [LCCIntranet_Config]
GO
DBCC CHECKDB(N'LCCIntranet_Config', NOINDEX)
GO
USE [library_SSP_Search_DB]
GO
DBCC CHECKDB(N'library_SSP_Search_DB', NOINDEX)
GO
USE [Librarysspdb]
GO
DBCC CHECKDB(N'Librarysspdb', NOINDEX)
GO
USE [seeunity]
GO
DBCC CHECKDB(N'seeunity', NOINDEX)
GO
USE [SharedServicesv2_DB]
GO
DBCC CHECKDB(N'SharedServicesv2_DB', NOINDEX)
GO
USE [SharedServicesv2_Search_DB]
GO
DBCC CHECKDB(N'SharedServicesv2_Search_DB', NOINDEX)
GO
USE [SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d]
GO
DBCC CHECKDB(N'SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d', NOINDEX)
GO
USE [WSS_Content]
GO
DBCC CHECKDB(N'WSS_Content', NOINDEX)
GO
USE [WSS_Content_lccintranet80]
GO
DBCC CHECKDB(N'WSS_Content_lccintranet80', NOINDEX)
GO
USE [WSS_Content_LCCSSPv2]
GO
DBCC CHECKDB(N'WSS_Content_LCCSSPv2', NOINDEX)
GO
USE [WSS_Content_library]
GO
DBCC CHECKDB(N'WSS_Content_library', NOINDEX)
GO
USE [WSS_Content_librarymysite]
GO
DBCC CHECKDB(N'WSS_Content_librarymysite', NOINDEX)
GO
USE [WSS_Content_libraryssp]
GO
DBCC CHECKDB(N'WSS_Content_libraryssp', NOINDEX)
GO
USE [WSS_Content_mysitev2]
GO
DBCC CHECKDB(N'WSS_Content_mysitev2', NOINDEX)
GO
USE [WSS_Search_ISSMOSS]
GO
DBCC CHECKDB(N'WSS_Search_ISSMOSS', NOINDEX)
Anyone had an issue like this?
Regards
Rob
View 2 Replies
View Related
Nov 14, 2007
W2k3SP2 running SQL2k5SP2 in mixed mode. Trying to run a daily maintenance plan with a domain admin service account [domainsqlsvc]. This account is having login failure when I try to run the maintenance job. Would like to figure out why this account cannot authenticate with SQL. I believe I have local login denied, but login as a svc and login as a batch job are both enabled in Domain Policy.
What am I missing?
TIA for your help!
-Nick
EDIT: Here is the error data.
Date 11/14/2007 9:36:16 AM
Log SQL Server (Current - 11/14/2007 9:36:00 AM)
Source Logon
Message
Login failed for user 'domainsqlsvc'. [CLIENT: <local machine>]
Error:18456, Severity: 14, State: 16
View 2 Replies
View Related
Apr 26, 2007
Ever since upgrading SQL Server 2005 to SP2 build 3050 all my maintenance plans which begin with a Check Database Integrity Task are failing! If I remove the task from the plan they all execute properly. If I run the T-SQL equivalent manually it executes properly.
I upgraded the server to build 3054 and rebooted several times and Im still having no luck. What am I doing wrong?
Thanks for your help with this.
-Kenny
View 7 Replies
View Related
Jul 19, 2007
Hi
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.
Any insight is greatly appreciated.
View 3 Replies
View Related
Apr 3, 2014
Our backups by default go to a network location, but I'd like to modify our maintenance plans to backup to an alternative location if the primary location isn't available. I've setup two Backup Database Tasks where the second one runs only if the first one Fails, and if the second one runs (on first one's failure) it then sends a notification to me so I know this occurred.
The Plan is running as expected, when I simulate a bad path in the first Backup Task the second one runs and the notification is sent, but the Job shows failure. I'd like to show the job as Successful when this occurs since I'm handling the issue and notification within the Plan, but I'm unable to find out how. I've set FailParentOnFailure to False on the Plan and I've changed the MaximumErrorCount to 2 with the assumption that this would work, but neither didn't.
Also I'm running into this in both SQL 2008 and SQL 2012.
View 1 Replies
View Related
Jan 10, 2001
hi
i executed sp_dboption,'my_db','offline','true'
and my_db went offline ok but when i cheked in enterprise manager list of db's i found my_db marked as (suspect/offline), i refresh the window and a got the same result but wen i close the enterprise manager and open it again my_db was not in the list of db's.Now i want to bring back my_db to online by executing sp_dboption 'My_db','offline','false' i was not able to do this and the command exist with this error the database is not in the list use sp_helpdb.
Please can anyone help me on this
Best Regards
Mamzy
View 1 Replies
View Related
Aug 28, 2002
I have a customer who's database was marked Suspect due to a drive failure that left a torn page. Before trying to reset the Suspect flag they took the database offline, and now we cannot get the database back online. Any ideas?
View 1 Replies
View Related
Nov 27, 1998
Hi all,
We have a serious problem.
The database in SQL Server 6.5 is marked offline
and the error log displayed the following messages :
udopen : file 'F:Filename.DAT' is incorrect size (614400 bytes, should be 537600)
udactivate (primary) : failed to open device 'F:Filename.DAT' for vdn 131
The entries for the device exists in sysusages and sysdevices.
Anyone encountered this before?
Please help if you know how we can get the database online, again.
Many Thanks.
View 1 Replies
View Related
Feb 28, 2006
Over the weekend I had one of my databases taken offline. To my knowledge no one explicitly took this database offline. This isn't to say that someone did and is refusing to speak up. I'm just trying to find some answers.
I do know that our IT dept. did maintenance to a number of our servers and they swear nothing was done to this one. So my question is, "Is there any SQL process that can take a database offline."
I've checked the log files but there isn't anything that directly points to that database being taken offline.
Running Windows 2000 Advanced Server SP4, SQL 2000 SP3 on a NT INTEL X86 platform.
Any ideas or comments are appreciated!
View 9 Replies
View Related
Mar 12, 2007
What exactly is database offline? I tried to google it but didn't get what I was looking for.
One of my environments has few users. They attach databases and run an application to produce the results. Sometimes they forget to detach databases after their use and these databases remain in SQL Server. Sometimes they leave the database to use it in future.
At the moment there are about 900 databases attached. I believe these attached databases are using the servers resources and therefore, decrease performance. Is this correct?
I was thinking to force the users to detach databases that they do not need and attach when they need. Then thought why shouldn't just set the database to offline and take it back online when they need. Does this increase performance? Does this save server's resources?
Thanks,
Canada DBA
View 1 Replies
View Related
Oct 1, 2007
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
View 3 Replies
View Related
Jan 24, 2002
Are there any known issues using Maintenance Plans to backup DBs in SQL Server 7.0
Thanks
View 1 Replies
View Related
Feb 23, 2000
I am doing full backups on 25 databases nightly using the SQL maintenance plan. Recently I have started getting the backup process hung on one database, so the whole plan does not finish. The job shows as executing, but nothing is being done, and nothing has been written to disk for this database. The SQL logs show a DBCC TRACEON 208, but the process it refers to is using another database. The NT logs do not show anything going on either. Additionally the backup process will not die when it is killed. They only way to make it go away is to stop and start the SQL Server. The system is a quad Pentium 450, 2Gig of ram, and plenty of disk space. It is NT SP5, SQL7 SP1. The DB is about 8.2 Gig. There are no other jobs scheduled to run at the time the backups run. Does anyone have any ideas what would be causeing the process to hang? Also what is a trace flag 208, it is not listed in BOL. Thanks for your help.
View 1 Replies
View Related
