Differential Backup Fails Because Of Erroneous Full Backup
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.
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
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...
Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.
We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.
We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)
When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).
Is there anything we are missing, why are the differential backup that big?
I want to set a full and differential backup to one database in sql server 2000. Is there a way to set both full and differential to just one database.
i want the full backup weekly once and differential every day to set up.
After some advice - I have a SQL Server 2005 database which is part of an anti-virus setup. The main database is 25Gb is size, and it is running in simple recovery mode. There are two backup jobs in place, one to do a differential backup each Mon-Sat, and one to do a full backup on Sun. Although the backups do get done they are taking 5 hours to do. Any wiz out there care to suggest what the problem is, I would've though that maybe an hour was more acceptable ?
I am running the following script to attempt a restore of a differential backup:
RESTORE DATABASE AdventureWorks FROM DISK='C:SQL2005_BackupsAutoBackupsAdventureWorks.bak' WITH NORECOVERY GO RESTORE DATABASE AdventureWorks FROM DISK='C:SQL2005_BackupsAutoBackupsAdventureWorksDiff.bak' WITH RECOVERY GO
I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")
The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:
BACKUP DATABASE AdventureWorks TO DISK='C:SQL2005_BackupsAutoBackupsAdventureWorks.bak'
The script for the differential backup is:
BACKUP DATABASE AdventureWorks TO DISK='C:SQL2005_BackupsAutoBackupsAdventureWorksDiff.bak' WITH DIFFERENTIAL, INIT
All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. (Sorry, I'm primarily a programmer, not a DBA) Can anyone help?
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
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?
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of: a) 8PM when the backup started b) 8PM with some of the changes made between the hour c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Hello, I have MS SQL 2005 server with 300+ databases on it. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, it can create 3-4 databases a day (random time). I've scheduled full backup of all databases to run once at night, and it runs just fine. Besides that, I have scheduled tran logs backup of all databases to run every hour. This backup fails from time to time with the following error:
Executing the query "BACKUP LOG [survey_p0886464_test] TO DISK = N'D:\backups\log backups\survey_p0886464_test_backup_200708072300.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0886464_test_backup_20070807230002', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. 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.
So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.
I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure?
Or is it possible to schedule full backup to run if tran log backup fails? Any advice will be much appreciated.
I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.
Is it true? Can anyone offer official documents?
In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.
If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?
Thru maintenance plan I backup my DBs every night and the T-Logs every hour. Now I have to set the option "select into/bulk copy" on one DB. I schedule the differential backup every 3 hour with option "overwrite existing media", wich is, in my case, one file on disk. My sequence of T-Logs is valid even if a non logged operations occurs? If I have to restore this DB can I use the last full backup + the differential backup + all the logs in between?
I trying to created a Differential backup and I get the following error. Can anyone help?
The volume on device 'd:MSSQL7BACKUPCPIDATABKUP.BAK' is not part of a multifamily (RAID) media set. Use WITH FORMAT to form a new RAID set. [SQLSTATE 42000] (Error 3259) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I'm trying to create a differential backup job that run hourly and that creates a different differential files. Ext. dbfilename1am, dbfilename2am, dbfilename3am ect... Can I do this with one job? Are do I have to create a differential backup job for each hour?
I've defined a maintenance plan in SQL2005, which creates a differential backup of a DB daily. Suppose I'm having differential backups from May, 10 to May, 14 but backup files of May 12 and May 13 are lost (deleted unexpectedly). Am I losing some data, if I restore from backup file of May 14?
I need to replication a server database out side a firewall. But can't change the server configuring for replication, so replication is out. So I wanted to know if I have a full backup of every db send to me every 24hrs. W/ differential every hr, will the restoring process work on the restoring server? Should I restored the full 1st? I'm not sure if the restoring of the Differential will work. Any help on the steps to take, to get this to work with backups & differential will be greatly appreciated.
I'm trying to run a differential backup on our new 7.0 box. It fails with an error message number 3242 "The file on Device 'E:MyFile' is not a valid Microsoft Tape Format backup set."
Why "Tape Format" if I said "Backup database [mydb] to disk"?..
When I run exactly the same query without "Differential" I got a full db backup.
When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.
Hi, Im trying to restore the differential backup,I have to restore the full backup first and then restore the differential backup.Can any body tell me the code for this?
my program is like this --for fullbackup if type=D begin restore database databasename from disk=path end --for differential backup else if type=I ???
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 create a full backup followed by daily differential backup, when I restore database from the fifth or later differential item, I get the following Error Message:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the earlier state. (Microsoft.SqlServer.Smo)
It seems that there is an error when the fifth differential backup occurred, It's a disaster, how can I do, help!
Where can I find more about Multibase Differential Backups? I searched through BOL and MSDN (http://msdn2.microsoft.com/en-us/library/ms178046.aspx) but there is too few information (missing sample, backup syntax for selecting differential base).
Is it possible to have two (or more) bases for differential backup for one database? How to preform such backups?
I have 30 databases on sql server 2005 that I need to do a full backup every morning at 7:00 and tran log backup every 30 minutes until 7:00 PM. If I create a maintenance plan for a backup using the wizard I have the option of starting a full backup at 7 am and then an option of doing tran log backups every hour using a different schedule. I plan on selecting the option to create a different folder for every database. I just need to confirm that in this way the way to restore the data would be
1. to restore a full backup
2. apply all the tran logs depending on the time they want to recover back to.
I just think this is the easiest approach to have 30 databases on the same backup scheme instead of creating a separate backup device for each database and doing a full backup on that device and appending all tran logs to that device which means just 1 bak file versus the above strategy with a number of tran log files. Please advise.
I recently started using Differential backups. They are working but are growing in size a lot quicker than I expected.
The backups are growing by 2.5GB every day although the total size of all transaction backups is under 350MB. I would have imagined that the total transaction log backups would be a good indicator of total database changes and therefore the differential backups would approach this figure.
