I have not been able to find any details about backup failures for
MSSQL 2000. I am researching the following:
1. when a backup fails at a certain point during the process does the
backup:
A. stop there having recorded some details?
B. quit without backing up any of the data it already processed?
C. skip the data it cannot read and move on?
2. Does the error log record any of the details above? For instance, if
there is a table that the backup could not write to .bak, does the
error log report exactly where the error was?
I need a way to obtain a Backup Exception 'Report' that when a backup
fails it can tell me what did not get into the .bak file. I would
prefer automatic notification. Our Oracle DBs have something setup
that can be run when a piece of the DB does not get backed up letting
the Oracle DBA know where there are issues.
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.
After upgrading a server from SQL 2000 to SQL 2005, I can't get any SQL Agent jobs to run, giving logon failed messages. MSSQLSERVER and SQLSERVERAGENT services are set up to run as the same domain account which has administrative rights to the server and is a SQL login with the sysadmin role. For basic testing purposes, I created a test job that all it does is run a T-SQL command 'SELECT @@VERSION'. The job is also set to run as the same account that the Agent is running under. Below are the logs (with trace messages on) from the point of starting the Agent to my attempt to run the job (user and server names have been changed to protect the innocent):
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168 2007-10-22 15:31:15 - ? [101] SQL Server TESTSERVER version 9.00.3042 (0 connection limit) 2007-10-22 15:31:15 - ? [102] SQL Server ODBC driver version 9.00.3042 2007-10-22 15:31:15 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is 2007-10-22 15:31:15 - ? [310] 4 processor(s) and 3328 MB RAM detected 2007-10-22 15:31:15 - ? [339] Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2 2007-10-22 15:31:15 - ? [431] Populating subsystems cache... 2007-10-22 15:31:15 - ? [432] There are 11 subsystems in the subsystems cache 2007-10-22 15:31:15 - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 80) 2007-10-22 15:31:16 - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40) 2007-10-22 15:31:16 - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 100) 2007-10-22 15:31:16 - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [124] Subsystem 'SSIS' successfully loaded (maximum concurrency: 400) 2007-10-22 15:31:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control 2007-10-22 15:31:16 - + [260] Unable to start mail session (reason: No mail profile defined) 2007-10-22 15:31:16 - ? [174] Job scheduler engine started (maximum worker threads: 400) 2007-10-22 15:31:16 - ? [193] Alert engine started (using Eventlog Events) 2007-10-22 15:31:16 - ? [146] Request servicer engine started 2007-10-22 15:31:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect 2007-10-22 15:31:16 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient... 2007-10-22 15:31:16 - ? [133] Support engine started 2007-10-22 15:31:16 - ? [167] Populating job cache... 2007-10-22 15:31:16 - ? [297] SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000] 2007-10-22 15:31:16 - ? [168] There are 2 job(s) [0 disabled] in the job cache 2007-10-22 15:31:16 - ? [170] Populating alert cache... 2007-10-22 15:31:16 - ? [171] There are 0 alert(s) in the alert cache 2007-10-22 15:32:59 - ? [177] Job Test1 has been requested to run by User DOMAINmyaccount 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp) 2007-10-22 15:32:59 - ? [184] Job completion for Test1 is being logged to sysjobhistory 2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
I am trying to install the client tools for management purposes only on a IBM T42 laptop which meets all the hardware and software requirements listed on MS support including the HCL list, the Readme, the forums and MSDN but the install still shows the following log messages when the install fails.
AdminMessage = Setup requires user to be in the administrator group in order to continue the installation process. Setup is aborting as the current user is not in the administrator group.
(I am an adminsistrator on this machine)
Property(S): SupportedOSMessage = Installation of this product failed because it is not supported on this operating system. For information on supported configurations, see the product documentation.
Property(S): SupportedProcTypeMessage = This Microsoft SQL Server Native Client package is not supported on the current processor type.
after restoring a database and regenerating keys, I'm trying to queue messages, and they're all hanging out in sys.transmission_queue with the following message:
Wanted to check if there's any option to suppress message after the database backup statement is run like we have in dbcc checkdb by using NO_INFOMSGS?
Processed 448 pages for database 'master', file 'master' on file 1. Processed 2 pages for database 'master', file 'mastlog' on file 1. BACKUP DATABASE successfully processed 450 pages in 0.100 seconds (35.151 MB/sec). Processed 280 pages for database 'model', file 'modeldev' on file 1. Processed 2 pages for database 'model', file 'modellog' on file 1. BACKUP DATABASE successfully processed 282 pages in 0.036 seconds (60.994 MB/sec). Processed 1992 pages for database 'msdb', file 'MSDBData' on file 1. Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Running SQL Server 7: Looking at the jobs under SQL Server Agent in Enterprise Manager, I notice that several of my backup jobs failed last night, is there anywhere I can look that will give me a more descriptive error message? I looked at the SQL Server Logs and didn't find anything that gives me any clue. Thanks. joe
I have backing SQL Server which consists of several database. The scheduled Maintenance plans backs up all the database except the larger one. Backup is across network at night. I get the following errors.
Write on '\tetnassqlbackupTetnetreports201006.BAK' failed, status = 121. See the SQL Server error log for more details.
looking at log gives the following details : (both associated to the same backup)
BACKUP failed to complete the command BACKUP DATABASE [Tetnetreports] TO DISK = N'\tetnassqlbackupTetnetreports_db_200610192302 .BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
and
BackupDiskFile::RequestDurableMedia: failure on backup device '\tetnassqlbackupTetnetreports_db_200610192302. BAK'. Operating system error 64(The specified network name is no longer available.).
Yet it allows me to BACKUP to LOCAL C: DRIVE without issues:eek:
HiMy DB backup and transaction backup of a database keep on failing withthe following error:Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBCSQL Server Driver][SQL Server]Write on'E:SqlServer estdb_db_200604221313.BAK' failed, status = 112. See theSQL Server error log for more details.[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE isterminating abnormally.Any idea what it really means ?ThanksDavid
I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the following is the error I am getting in the Logfile, can some one please through some light on this.
" failed with the following error: "Cannot open backup device \shq-ss2sql backupSQL_PRODDBWorkFlow_backup_200708291911.bak. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.
I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.
I seem to have a problem with my SQL Server log backup system. First I was receiving alerts that the log file was filling up, so I increased it from 3Mb to 150Mb, but then I got another alert saying the same thing, later on I got an alert saying that the log backup had failed. I then decided to truncate the log and do a full backup after as suggested, however this did not work, I don't know if I am doing something wrong, this is really confusing and frustrating.
I have scheduled transaction log backups to occur every 15 minutes with database backups every sunday at 1.00AM and differential db backups every day at 1.30AM.
While viewing the job history of the transaction log backups through enterprise manager, I noticed that one transaction log backup had failed at 1.00AM on sunday. The error message was,
"Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. "
Please let me know what the error message implies. Will there be any problem if the transaction log backup takes place at the same time as the full database backup or diff. db backup?
Help! I am getting a Job failure for a DB Backup Job when I open up the EM -> Management -> SQL Server Agt -> Jobs When I check the SQL Logs, there is no Error message, nor is there an error message in the DB Maintenance Plan History. I checked the Server to make sure the DB was being backed up, and the current file is there. Does anybody have any suggestions? Thanks! (make me look good to the rest of my group!)
Greetings,I am seeking information related to this subject.BOL suggests backing up the active transaction log immediately after afailure, so that the backup can be used in a recovery scenario if necessary.This is the relevant text from BOL "Transaction Log Backup":----//The transaction log backup created at 8:00 P.M. contains transaction logrecords from 4:00 P.M. through 8:00 P.M., spanning the time when thedatabase backup was created at 6:00 P.M. The sequence of transaction logbackups is continuous from the initial database backup created at 8:00 A.M.to the last transaction log backup created at 8:00 P.M. The followingprocedures can be used to restore the database to its state at 10:00 P.M.(point of failure).Restore the database using the last database backup created.1.. Create a backup of the currently active transaction log.---// end of excerptIf the failure results in loss of the instance and/or the log's parent db,how would we back up that log? Even if we could, what confidence would wehave that the backed up log was not corrupted at failure time?Thanks,PSG
I have recently started receiving failures on a differential backup thatpreviously succeeded. Nothing has changed with the structure of the db.Here is the message, any ideas are welcomed. Thanks.Executed as user: fsafood-netisqlservice. The backup data in'E:SQL_BackupsBiz_SalesBiz_Sales_Diff.bak' is incorrectly formatted.Backups cannot be appended, but existing backup sets may still beusable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminatingabnormally. [SQLSTATE 42000] (Error 3013) Associated statement is notprepared [SQLSTATE HY007] (Error 0) The media family on device'E:SQL_BackupsBiz_SalesBiz_Sales_Diff.bak' is incorrectly formed. SQLServer cannot process this media family. [SQLSTATE 42000] (Error 3241)VERIFY DATABASE is terminating abnormally. [SQLSTATE 42000] (Error3013). The step failed.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I am having a problem with a regular backup of an SQL Server (MSDE2000) database to a local drive. I initiate the backup once a week, byissuing the required T-SQL, via ADO. In this case, the T-SQL is:BACKUP DATABASE GPRS_Dimensioning_Archive TO local_backup WITHRETAINDAYS=21, NAME='GDA_20040706'Note that "local_backup" is a file sitting on the same physical driveas the database itself, and has > 80 GB free. It is not a RAID device.The database that I am backing up is 1.6 GB. Oddly, the backup ofanother database to the same file was successful; it was ~900 MB.Even more strangely, I also back these databases up to a networkdrive, which has been running successfully for a number of weeks now(longer than the local backup), and continues to work without anytrouble. The resultant backup file is much larger than the one sittingon the local drive (6.8 GB on the network drive vs ~4 GB locally) Thisnetwork drive has less space (~50 GB) and is configured as RAID 0. Itis a Linux box, using Samba to present the drive to the Windowsnetwork.Any ideas?? I am completely stumped. Obviously, Microsoftdocumentation has been particularly unhelpful.The excerpt below is taken from the SQL server error log, covering acouple of attempts.Any help would be greatly appreciated!!!Nick Tompson.2004-07-06 09:56:01.99 spid54 BackupMedium::ReportIoError: writefailure on backup device 'C:Front EndLocal BackupDB_Backup.bak'.Operating system error 112(error not found).2004-07-06 09:56:02.01 spid54 Internal I/O request 0x02672360: Op:Write, pBuffer: 0x02890000, Size: 65536, Position: 4294914560, UMS:Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFF3200, OffsetHigh:0x0, m_buf: 0x02890000, m_len: 65536, m_actualBytes: 0, m_errcode:112, BackupFile: C:Front EndLocal BackupDB_Backup.bak2004-07-06 09:56:02.01 backup BACKUP failed to complete the commandBACKUP DATABASE GPRS_Dimensioning_Archive TO local_backup WITHRETAINDAYS=21, NAME='GDA_20040706'2004-07-06 09:56:17.08 spid54 Starting up database'GPRS_Dimensioning_Archive'.2004-07-06 09:56:33.11 spid54 BackupMedium::ReportIoError: writefailure on backup device 'C:Front EndLocal BackupDB_Backup.bak'.Operating system error 112(error not found).2004-07-06 09:56:33.11 spid54 Internal I/O request 0x02674360: Op:Write, pBuffer: 0x028A0000, Size: 65536, Position: 4294914560, UMS:Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFFF3200, OffsetHigh:0x0, m_buf: 0x028A0000, m_len: 65536, m_actualBytes: 0, m_errcode:112, BackupFile: C:Front EndLocal BackupDB_Backup.bak2004-07-06 09:56:33.11 backup BACKUP failed to complete the commandBACKUP DATABASE GPRS_Dimensioning_Archive TO local_backup WITHRETAINDAYS=21, NAME='GDA_20040706'
I have a job that runs full backs and that was successfull but all my transaction log backups failed. This is the error message i get:
1.VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had elapsed.)
2.SQL error 3013: BACKUP LOG is terminating abnormally.
3.SQL error 4214: BACKUP LOG cannot be performed because there is no current database backup.
DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.CREATE PROCEDURE [dbo].[spu_Import_Export_Image](@srvr varchar(50),@db varchar(50),@usr varchar(15),@pwd varchar(50),@tbl varchar(50),@col varchar(50),@mod varchar(1),@imgpath1 varchar(1000),@pk varchar(50))ASBEGINdeclare @path varchar(50)declare @whr varchar(200)declare @fil varchar(100)declare @cmd varchar(1000)declare @imgpath varchar(800)declare @ext varchar(5)--declare @pk varchar(50)declare @KeyValue varchar(8000)declare @image varchar(50)--declare @imgpath1 varchar(1000)declare @imgpath2 varchar(1000)declare @sellist varchar(2000)set @path = 'c: extCopy.exe'select @sellist = 'DECLARE curKey CURSOR FOR SELECT ' + @pk +' FROM '+ @tbl + ' ORDER BY ' + @pkexec (@sellist)OPEN curKeyFETCH NEXT FROM curKey INTO @KeyValueWHILE (@@fetch_status = 0)BEGINset @whr = '"where '+ @pk +' = "' + @KeyValueset @fil = @imgpath1 + '' + @KeyValue --+ @extset @cmd = @path + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr+ ' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr + '/F ' + @fil+ ' /' + @modexec Master..xp_cmdShell @cmdFETCH NEXT FROM curKey INTO @KeyValueENDCLOSE curKeyDEALLOCATE curKeyENDGOAbove srcipt runs fine with image data type in one table but when irun for some other table it gives me Error MessageTEXTCOPY Version 1.0DB-Library version 8.00.194SQL Server 'WSQL01' Message 170: Line 1: Incorrect syntax near '99'.(Concerning line 1)DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.ERROR: Could not use database 'test1'NULL-----------Aslo it only runs on server console if i run it from workstation uingsame files and tables it gives me an error again. Can anybody help meand reply me at Join Bytes! asap.thnx,dharmesh
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup... Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN] [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup. [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
According to our stratergy we are taking trans log backup of every user database everuy two hours.things seem to work great and with success at the day time and at night once at 12.03 and 2.03 they fail and when i checked the error log i found that there is only one database for which its failing and it gives the following error.
Database ors: Transaction Log Backup... Destination: [D:BackupTranLogorsors_tlog_200108280203.TRN] [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4213: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot allow BACKUP LOG because file 'ors_Data' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup. [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.
We had a option called select into/bulccopy set which i disabled once this failure has occured but still the problem persists....there are no other options set...i am out of resources i donno what to do..could you suggest me something and how to solve this problem its happening in prod.
As Said by someone i took a full backup...tooooo it does not solve the issue.
I'm getting "Executed as user: SPIESQLService. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." on the TRN backup portion of the maintenance plan for the msdb and model databases. On review of files created it's clear that the msdb trn log backup is failing, but there's no other error to indicate the underlying problem.
Can someone help me with this error? It only occurs when I try to backup to the UNC name (which is local to the machine). When I do it with a drive letter, I have no problem.
My DB recently goes weird after the Hard Disk failed and replaced last week. The SQL have 3 database running on my computer, DB1, DB2 and DB3. my computer has Disk C and Disk D, they both work fine that i can save and delete on them both. But now, the database backup of DB1 fails everytime, first i thoguth it's the Hard DIsk's problem, but after i tried to backup the other DB (DB2 and DB3) on Disk D, it works fine....therefore...i am here...without any idea. What even weird is, I can backup my DB1 on Disk C without any problem, but just can't do it on Disk C. Can anyone help ~ thank you
Here comes the error message i got : BackupMedium::ReportIoError: write failure on backup device 'D:DB_BAKmy_DB1.bak' Operation system error 2 (The system Can't find the file)
Recently migrated from 2008 to 2012. Everything is working fine; however went to take my first set of backups today and one of them blew up:
Executing the query ...
Backup and restore errors: Unexpected number of disk files associated with database object. Possible cause for that is missing or corrupt cryptokey.bin or detach.log file.
Execution complete
Not finding much online on the specific error.
- Data source is valid - Can process the cube - Can query / navigate cube
Next step would be drop/recreate; however this is an older cube that I'd prefer to leave alone at this stage if possible.
I am getting a failure on the db backup job of one of my maintenanceplans. It is coming back with the generic error message of,"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The stepfailed."I then checked the Database Maintenance Plan History page, but thisshows all the steps having run successfully. If I check the drives forthe actual backup files, they exist and look healthy too!There is plenty of space on the drives, so it is not that.I've checked the NT logs and all they say is,"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DBMaintenance Plan for All User Databases''(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:2005-06-29 21:00:04 - Message: The job failed. The Job was invoked bySchedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "Which doesn't give me any clues.Since the backups have actually run to success, I am going to take offthe option on the maintenance plan to "Verfiy the integrity of thebackup upon completion". Maybe it is this that is causing problems,rather than the backup?Anyone had anything similar?
Hello, My master database (MS SQL 2005) has simple recovery model, however, when performing diff backup of "all databases", I am getting the following error:
Executing the query "BACKUP DATABASE [master] TO DISK = N'X:\Database Backups\diff backups\master\master_backup_200803251235.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'master_backup_20080325123514', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I choose to back up only "All user databases", then it performs just fine. Why I cannot run diff backup of master?
Im planning to setup sqlserver 2005 log shipping between two servers.The size of the database is around 50GB and we are taking transaction log backup and shipping at 30mins interval.
Suppose if the backup job couldnt take the transaction log backup with in the specified interval ie 30mins,then how does the copy and restore job works. Will it give an error saying tht the transaction log could nt be copied and restored ??? How to deal with this kind of situations where the backup is taking more time than the threshold time limit and ur copy,restore fails. Is there a way to handle this kind of situation in logshipping Sqlserver 2005. and how does it affect the Secondary database. ??
I've got a couple of backup database tasks setup to run one after the other. The are set up the same, Alldatabases, backup to a device each, and overwrite every time. The task on databases full works fine but the one on logs fails every time, right away with the following rather esoteric error pulled from logging in the event log. Event Type: Error Event Source: SQLISPackage Event Category: None Event ID: 12550 Date: 29/01/2008 Time: 09:54:11 User: OILCATSsqlservice Computer: GUNKVBPROC Description: Event Name: OnError Message: The Execute method on the task returned error code 0x80004003 (Object reference not set to an instance of an object.). The Execute method must succeed, and indicate the result using an "out" parameter.
Operator: OILCATSSqlService Source Name: Back Up Database Task 1 Source ID: {BF117128-FB4C-470D-AC00-4C68797C2C97} Execution ID: {450BA60C-2EBF-453B-B3E4-80FBC2042F83} Start Time: 29/01/2008 09:54:11 End Time: 29/01/2008 09:54:11 Data Code: -1073594108
What is this elusive Execute method that is only relevant to the log aspect of this task?
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for : 1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well. 2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters. 3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken. I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure : CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn = CASE WHEN @inMonth = 'January' THEN 'hitsInJan' WHEN @inMonth = 'February' THEN 'hitsInFeb' WHEN @inMonth = 'March' THEN 'hitsInMar' WHEN @inMonth = 'April' THEN 'hitsInApr' WHEN @inMonth = 'May' THEN 'hitsInMay' WHEN @inMonth = 'June' THEN 'hitsInJun' WHEN @inMonth = 'July' THEN 'hitsInJul' WHEN @inMonth = 'August' THEN 'hitsInAug' WHEN @inMonth = 'September' THEN 'hitsInSep' WHEN @inMonth = 'October' THEN 'hitsInOct' WHEN @inMonth = 'November' THEN 'hitsInNov' WHEN @inMonth = 'December' THEN 'hitsInDec' END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)' if exists(select * from tblTracking where ntid = @ntid and @page = page) begin if exists(select * from tblTracking where lastVisit = @thisHit) begin -- DO NOTHING! end else begin DECLARE @theColumn varchar (100) SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page' SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1 SET @monthCount = (Exec @theColumn) SET @monthCount = @monthCount + 1 SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page' Exec @updString end endelse begin Exec @insString endGO And to my surprise, the only 3 errors I got were : Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'. However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
