Database Backup Has Stopped Working. Cannot Tell Why
Mar 31, 2007
Hi,
I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.
But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.
I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.
Any help will be appreciated.
Thanks
Sam
View 3 Replies
ADVERTISEMENT
Jan 11, 2001
Our stored procedure error handling is not working now but did before.
When a severity level 16 (Miscellaneous user error) occurs the batch (stored procedure) exits without processing the next statement which is the error check routine. This is a simple error processing routine and used to work. What could have changed?
I have searched books online and have found nothing on abnormal termination of a SQL batch. I would appreciate some help here.
Loren
View 3 Replies
View Related
Oct 4, 2004
I am having a real problem opening a database on a SQL server through EM. The DB is on the machine as I can use EM on another machine and login from there. However, when I try to login to the DB via EM on the actual server I cannot and get an error saying 'Connection failed. Check SQL Server registration properties'.
Even if I change the reg properties it still will not let me access the database, it just keeps giving the same error.
Has anyone seen something like this before?
View 2 Replies
View Related
Jan 28, 2008
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million
Current record count in t_DTM_DATA_1 - 267 Million
Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP]
,
,[MAJ]
,[MINR]
,[LOCN]
,[DPT]
,[YEAR]
,[PD]
,[WK]
,[TRDT]
,[SYSTEM]
,[AMOUNT]
,[DESCRIPTION]
,[GROUP]
,[VENDOR]
,[INVOICE]
,[IDAT]
,[PO_NUMBER]
,[DDAT]
,[RCV#]
,[RDAT]
,[RSP]
,[EXPLANATION]
,[UPLOAD_DATE]
,[UPLOAD_USER]
,[UPLOAD_NAME]
,[RELEASE_DATE]
,[RELEASE_USER]
,[RELEASE_NAME]
,[TRTM])
SELECT
t_DTM_DATA_STAGING.CP,
t_DTM_DATA_STAGING.CO,
t_DTM_DATA_STAGING.MAJ,
t_DTM_DATA_STAGING.MINR,
t_DTM_DATA_STAGING.LOCN,
t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR,
t_DTM_DATA_STAGING.PD,
t_DTM_DATA_STAGING.WK,
t_DTM_DATA_STAGING.TRDT,
t_DTM_DATA_STAGING.SYSTEM,
t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION,
t_DTM_DATA_STAGING.[GROUP],
t_DTM_DATA_STAGING.VENDOR,
t_DTM_DATA_STAGING.INVOICE,
t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER,
t_DTM_DATA_STAGING.DDAT,
t_DTM_DATA_STAGING.RCV#,
t_DTM_DATA_STAGING.RDAT,
t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME,
t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
View 4 Replies
View Related
Aug 21, 2007
I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.
The actual command i am executing is a simple
sp_addpublication_snapshot
but it fails with
Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!
I am using SQL 2005 SP2 if that makes any difference.
Thanks
Andy
View 6 Replies
View Related
Feb 12, 2002
My MaintPlan 1 has stopped working.
Resetting the time to an hour in the future, does not start.
Tonight I'm going to stop and start MSSQL server.
If that does not start the MaintPlan 1, then I'll rebuild a new maint plan.
Is there any way to tell why it may have failed?
I've checked the error logs, the maint plan logs and NT 4.0 sys logs, all indicated the last backup date / time with no other relevant notations.
TIA
JeffP....
View 2 Replies
View Related
Jun 18, 2008
Hi,
I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.
I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.
This is the line causing the problem (if I take it out, the rest of the SP works no problems):
--write the text file
EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:ewslettertext extbody.txt -U -P -c'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL
Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?
Any help would be greatly appreciated!
Thanks
View 6 Replies
View Related
Dec 23, 2005
I just upgraded the .NET 1.0 to 1.1 today and now when I launch the Web Data Administrator for MSDE it hangs up as soon as I hit the login button.
I reinstalled MSDE and the Web Data Administrator and that didn't correct the problem.
Help!
View 1 Replies
View Related
Apr 29, 2008
I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:
CREATE PROCEDURE xxxxx
WITH EXECUTE AS 'domainusername'
AS
EXEC msdb.dbo.sp_start_job B'jobname' ;
RETURN
The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.
This worked for the past several months.
Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.
Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.
Thanks - SM
View 3 Replies
View Related
Mar 27, 2008
I'm using query notifications and it worked fine as long as I just edited the data that the "query points to" (one table). That is, my graphical represenation of the data (in a gridview) was updated correctly with the new data 1ms after I changed it, just like it should.
When I inserted a new row to the table the query notification stopped working, meaning my gridview wasn't updated. Any idea of why? Also, after this, not even editing of the data made the query notification trigger so it must have totally stopped.
Thanks in advance
ripern
View 3 Replies
View Related
Aug 30, 2006
I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.
At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed.
I am not sure what triggered it to stop working. I cannot get anything on the log
Any help is appreciated.
View 2 Replies
View Related
Mar 28, 2006
Hello...
We have had Merge Replication working for the past few months (SQL 2005 to SQL Mobile) and suddenly today the replication URL that points to the SqlCESA30.DLL does not work. I have tried re-running the SQL Mobile virtual folder wizard a few times....resetting IIS and even a re-boot...and still the URL is not available.
Is there anything that might provide some clue as to why this has unexpectedly failed?
thanks for any help!
- will
View 3 Replies
View Related
Mar 17, 2004
Hi, I used to be able to debug stored procedures via Visual Studio.net 2003. However, this has stopped working. It does not produce an error just simply doesn't work anymore i.e. the breakpoints are by-passed.
I have the correct settings in the Debug configuration section. If any-one knows how to rectify this your help would be appreciated.
I have thought about re-installing the remote debugging functionality on the server. However, our Visual Studio.net discs are with a developer who is away at present.
Thanks in advance
Lee
View 2 Replies
View Related
Jan 19, 2008
Reinstalling SQL Express did the trick
Thought this might help others..
View 2 Replies
View Related
Sep 20, 2006
I'm trying to retrieve native character data (SQL_WCHAR) through ODBC into an SQL_C_CHAR buffer using SQLBindCol and SQLFetch.
The database is SQL Server 2005 Developer Edition.
If I use DRIVER={SQL Server}, i.e., use SQLSRV32, everything gets converted properly.
If I use DRIVER={SQL Native Client}, i.e., use SQLCNLI, I apparently get garbage. This is true for ntext, nchar and nvarchar. I also have problems with xml, but I haven't gotten far enough to isolate what's going on with it.
View 3 Replies
View Related
Mar 16, 2004
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.
On P5 I created the Backup Device:
USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\P3 emp est.bak'
Then I executed the following on P5 Query Analyzer:
Backup Database myDatabase TO myDevice
But I get this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any idea? Thanks and regards.
View 4 Replies
View Related
Sep 4, 2007
We have a Prinicipal, a Mirror and a Witness server. We have automatic failover configured between the Prinicipal and Mirrored server. When we stop MMSQL service on Prinicipal, not all the databases failover to the Mirrored instance.
Any suggestions would be welcomed as we have a tight deadline to get this in Production.
View 5 Replies
View Related
Dec 8, 2003
I'm hoping someone can explain to me exactly what the SQLConnection.Open() method does, especially when the database is stopped. I'm trying to include some error processing in my program, specifically to ensure the database is up and running. But even with the database stopped, the Open() statement works fine. Later, when I try to read from the database, I get the error. I'd like to stop it before it gets any further. Why is the Open() statement "working" even with the database stopped?
View 6 Replies
View Related
Apr 23, 2006
Hi,
I've a very basic doubt about database mirroring. I did setup a database mirroring session with the help of SQL Server Management Studio between the server A (db1) and B(db1). When is stop database mirroring by using the command button "Stop Mirroring" available in the mirroring page of SQL Server Management Studio , the mirror database{ B(db1) } goes to state "Restoring...". After stopping the mirroring i'm not able to access the mirror database.
Can you please tell me how to bring mirror database B(db1) to operation mode so that we can start working with that database?
Regards,
Gopi
View 1 Replies
View Related
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
View 8 Replies
View Related
Jan 31, 2008
Hi there
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?
Thanks
Jane
View 3 Replies
View Related
Jan 13, 2000
Hi,
How do we know that tape backups is working. And how do we know what is on them.
dindu
View 1 Replies
View Related
May 13, 2008
Hi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
View 3 Replies
View Related
Apr 15, 2002
I had a number of jobs which were backing up system and user databases under SQL 7 using SQL ServerAgent. Over the weekend I upgraded SQL 7 to SQL 2K.
All backups work except for one, which gets the following error message: Executed as user: SANDIEGOSvcSQLServer. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
As best as I know SQLSTATE 42000 is an ODBC message indicating a syntax violation or an access violation. I have recreated the maintenance plan in an attempt to rule out a syntax violation. During the upgrade process I did not change the account under which SQL Server Agent runs, so it seems like it should not be an access violation.
Any ideas would be appreciated!!!
Thanks Gary Andrews Andrews_gary_w@solarturbines.com
View 1 Replies
View Related
Aug 21, 2006
I am using SQL server 7.0. After restoring all the databses (Exceptdistribution) Everything seems to be working fine except the backupmaintenance plan. I put following detail in the maintenance plan"General Tab:Plan Name --- DB Maintenance Plan1() All DatabasesOptimizations tab:Reorganize Data and Index PageChange free space per page percentage to 10%Remove unused space from database filesShrink Database when it grows beyond 50 MBAmount of free space to remain after shrink 10% of the data spaceSchedule: Occurs every 1 week(s) on Thursday, at 1:30:00 PM.Integrity tab:Chack database integrityInclude indexes.Attempt to repair any minor problemSchedule : Occurs every 1 week(s) on Thursday, at 1:00:00 PM.Complete Backup tab:Backup the database as part of the maintenance planVerify the integrity of the backup upon completionDisk use this directory: d:SQL BackupsBackup file extension: BAKSchedule: Occurs every 1 day(s), at 2:10:00 PM.Transaction Log Backup tab:schedule: Occurs every 1 day(s), at 2:00:00 PM.Reporting tab:Write report a text file in Directory: D:SQL BackupsLOG "Problem:My databases(mroduction as well as systems) sre not at all gettingbacked up according to above plan.Error Message in the Log Directory:"Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO(ODBC SQLState: 42000)] Error 4062: [Microsoft][ODBC SQL ServerDriver][SQL Server]Cannot open user default database '<ID>'. Usingmaster database instead."In the SQL server registration properties the login through whichgetting logged into the EM for that, default database is "my productiondatabase".Is this problem because of the distribution db which I am not able torestore.Please helpDeepak Sinha
View 1 Replies
View Related
May 13, 2008
Hi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
View 7 Replies
View Related
Oct 17, 2006
Newbie to backups here. I'm trying to setup a Maintenence Plan for backing up the system tables. For some reason the Differential backup on the Master table is failing.
Can you NOT run differential backups on the Master table?
Thanks.
View 1 Replies
View Related
Jul 15, 2015
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies
View Related
Nov 16, 2015
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies
View Related
Aug 4, 2007
Backup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies
View Related
Apr 1, 2008
I should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
View 3 Replies
View Related
Oct 17, 2006
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
View 3 Replies
View Related
Feb 10, 2006
Hi All,I am facing this issue very frist time. I add a logical device asfollowing:USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksData','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksData.bak'-- Create a logical backup device, AdvWorksLog.USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksLog','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksLog.bak'-- Back up the full AdventureWorks database.BACKUP DATABASE AdventureWorks TO AdvWorksData-- Back up the AdventureWorks log.BACKUP LOG AdventureWorksTO AdvWorksLogThe database backup is completed successfully but log's backup failswith the following messages:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current databasebackup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.We are moving from SQL 2000 to SQL 2005. I have ensured that databasehas full recovery mode on. We are using SQL 2005 on Windows 2003 ServerSP1.Further more, I also created a plan to test it, and plan also failswhen backing up the logs.Can anyone shed some light on this issue.Thanks in advance.Najm
View 1 Replies
View Related
