| Author |
 Topic  |
|
|
nentwich
Seasoned But Casual Onlooker
32 Posts
Status: offline |
 Posted - 12/20/2011 : 3:11:00 PM
|
Can anyone help me with this issue I am having in SQL 2005. I have looked around on the web and have not been able to find a solution as of yet.
I have a SQL server which hosts a number of databases. I have two maintenance plans. One plan/task is a weekly full database backup of all the database on the system. The other plan/task is an hourly transaction log backup of all the databases on the system. In order for a desktop application to work one of the databases' had to have the "compatibility level" set to "SQL Server 7.0". For some reason the hourly backup plan/task fails when backing up this database. But he weekly full backup is successfull. Could anyone give me some insight on how to fix the hourly maintenance plans?
Thanks for everyone's help.
Joel
|
|
|
cj_berlin
Honorable But Hopeless Addict
Germany
3964 Posts
Status: offline |
Posted - 12/21/2011 : 02:23:51 AM
|
Joel,
is the database in question set to Full Recovery mode?
FWIW, |
Evgenij Smirnov
|
 |
|
|
nentwich
Seasoned But Casual Onlooker
32 Posts
Status: offline |
Posted - 12/21/2011 : 05:06:04 AM
|
| Yes the databse is set to full recovery mode. |
|
|
|
Curt
Moderator
USA
6652 Posts
Status: offline |
Posted - 12/21/2011 : 5:07:34 PM
|
use yourdatabase
select * from sysfiles
backup log yourdatabase to disk = 'f:\yourdatabase.trn'
Run this and tell us what you get.
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
|
nentwich
Seasoned But Casual Onlooker
32 Posts
Status: offline |
Posted - 12/22/2011 : 05:39:31 AM
|
I receive the following error:
Msg 4214, Level 16, State 1, Line 3
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
Look back at the backups of this database I had a successful full backup last Sunday, 12/11 at midnight. So I am not sure why I am receiving this error message.
Joel |
|
|
|
cj_berlin
Honorable But Hopeless Addict
Germany
3964 Posts
Status: offline |
Posted - 12/22/2011 : 05:47:30 AM
|
| Try doing a manual full backup, i.e. not as part of a maintenance plan. Had seen this just yesterday. |
Evgenij Smirnov
|
|
|
|
Curt
Moderator
USA
6652 Posts
Status: offline |
Posted - 12/22/2011 : 07:39:23 AM
|
Ok, that is what I expect to see if you have just changed the recovery mode and have not done a full backup of the MDF file.
The Transaction log can not be backed up until the full backup of the Database file is done first.
Now do a backup of the data base.
backup database yourdatabase to disk = 'f:\yourdatabase.bak'
Then backup the transaction log:
backup log yourdatabase to disk = 'f:\yourdatabase.trn'
Tell us if it succeeds.
quote:
Originally posted by nentwich
I receive the following error:
Msg 4214, Level 16, State 1, Line 3
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
Look back at the backups of this database I had a successful full backup last Sunday, 12/11 at midnight. So I am not sure why I am receiving this error message.
Joel
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
|
nentwich
Seasoned But Casual Onlooker
32 Posts
Status: offline |
Posted - 12/22/2011 : 12:17:09 PM
|
Curt thank you for the addition information. I did as you stated. I ran a full backup and then a backup log job. I recieved the following when I ran the backup log job:
1 1 151648 -1 10 2 0 ConvertDatabase_1new D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database.mdf
2 0 96 -1 10 1048642 0 ConvertDatebase_1new_log D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_1.ldf
Everything looked good and I thought the issue had resolved itself. But during the next hourly backup job it failled again on the same datebase.
Joel |
|
|
|
Wiseman82
Old Timer
United Kingdom
520 Posts
Status: offline |
Posted - 12/22/2011 : 1:27:40 PM
|
It sounds like you might have a maintenance job scheduled that's breaking the log chain.
The two common things that break the log chain are switching to simple recovery model or backing up the log with truncate_only.
See what jobs are scheduled to run. If it's switching to simple recovery model it could be part of some maintenance script or import job. Also, check the sql server log as it might give you an indication when it's happening. |
David Wiseman
MCSE (2000/2003), MCSA (2003), MCDBA, MCITP:Database Administrator
 |
|
|
|
nentwich
Seasoned But Casual Onlooker
32 Posts
Status: offline |
Posted - 12/25/2011 : 12:42:26 PM
|
| I was doing a little experimenting last night. In order for the desktop app to use the SQL db it has to have a compatibility level of 7.0 set. When I set this compatibility level on the db I do not see the db listed in the list of db to be backed up by the maintenance job. As soon as I change the compatibility level to 2000 (80) the db shows up in the list. So I guess the question is how to do backup a db who compatibility level is set to 7.0 in SQL 2005? |
|
|
| |
Topic |
|
Help with a SQL 2005 Backup issue
