Greetings,
I am seeking information related to this subject.
BOL suggests backing up the active transaction log immediately after a
failure, 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 log
records from 4:00 P.M. through 8:00 P.M., spanning the time when the
database backup was created at 6:00 P.M. The sequence of transaction log
backups 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 following
procedures 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 excerpt
If 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 we
have that the backed up log was not corrupted at failure time?
We have (had) an active/active cluster. 2 physical machines, each running their own instance, clustered together. Node1/Ins1 and Node2/Ins2.
Node2 failed and Ins2 failed over to Node1 as it should. Node2 required that we rebuild the server (rebuild = reinstall O/S). Now we need to get Node2 back into the cluster and get Ins2 failed back over to Node2.
Does anyone know, for certain, the correct steps to accomplish this? Obviously, we could backup everything and completely destroy Ins2 and recreate it on Node2 then rejoin the cluster. But I'm looking for something less destructive.
Is it possible to reinstall SQL, then rejoin the cluster, and then fail Node2 over? Or will there be registry conflictions?
Any help would be appreciated. Also, if you have any links to some official documentation, that would be great too.
Is there a way to find active connection to a database so you don't have to wait for a restore or similar operation to fail. (Which take forever).?
Is there a SMO method or a transact SQL command or something that can be used in an application to detect active connections?
I don't want to have to wait forever to get a failure when you can't perform a operation that require that there be no active connection such detach a database. I want to be able to tell when I can perform a operation which requires no active connection and when I need to use a task without out dropping the connection like copying the schema using SMO methods.
However if there are no connection I can perform a detach copy and attach to create a new database which is considerable faster.
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!)
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.
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.
I have not been able to find any details about backup failures forMSSQL 2000. I am researching the following:1. when a backup fails at a certain point during the process does thebackup: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, ifthere is a table that the backup could not write to .bak, does theerror log report exactly where the error was?I need a way to obtain a Backup Exception 'Report' that when a backupfails it can tell me what did not get into the .bak file. I wouldprefer automatic notification. Our Oracle DBs have something setupthat can be run when a piece of the DB does not get backed up lettingthe Oracle DBA know where there are issues.Thank you,Stephanie
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?
The problem is as follows. SQL Express 2008 R2, backup is made by the means of bat file and sp. Need to have a table that would hold columns like 'date_of_backup', 'duration', 'message' (the one that appears on 'messages' tab in the result area after backup completion/failure). This is an example of real message that I want to catch:
Server: Msg 911, Level 16, State 11, Line 1 Database 'not_existing_db' does not exist. Make sure that the name is entered correctly. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
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.
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime. OpDatesessionStartTimesessionCloseTime 2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166 2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442 2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853 2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour, Count(*) AS Total FROM #empSessionLog Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults: opDateopHourTotal 2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:
My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.
Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.
Is it possible to configure two quorum disks, one for each group? What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?
