| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2010-06-03 : 00:03:40
|
| Hi,I created a maintenance plan(Full backup and Hourly Transaction Log Backup) in SQL Server 2005, the backup file was set to the default location: “Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”. From the SQL Server Agent, the Job History is showing the jobs succeeded. But when I open the Backup folder, I only see the .bak file, the .trn file is not there. I changed the file location of Hourly Transaction Log Backup, the .trn file is still not there, even the Job History is showing the jobs succeeded for the new location.Can some one tell me what happened to the maintenance plan and where is the .trn backup file?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2010-06-03 : 10:09:48
|
| Thanks Tara,I ran the script you provided in System Databases/msdb and I got nothing!So, that means the Transaction Log Backup didn't run. But, why I got jobs succeeded in Job History?Is there any way to fix it?Thanks. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 10:26:41
|
"So, that means the Transaction Log Backup didn't run."Are you sure the Backup Filename was ".trn" ?More normally backup files have ".BAK" extension - but they can be whatever you make them, of course  Perhaps look for files with a date/time around when the backup was made.See script here for backup history:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265 |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2010-06-03 : 10:49:43
|
| Thanks Kristen,Surely I setup the maintenance plan to make a Full backup with “bak” as Backup file extension, Transaction Log backup with the “trn” as Backup file extension. The problem is I have the full backup files (.bak) as designed, but the .trn file is not there. Both jobs were showing “succeeded” in Job History. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 10:54:49
|
| I would still query the backup history to see what file, if any, was created around that time.maybe the file was created, and then something deleted it (e.g. the retention policy was set very short). Knowing what SQL thinks!! it did may help solve the problem.You might have a file ending in "trn" rather than ".trn" ... or ".trm" [typo] or somesuch ... |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2010-06-03 : 11:54:57
|
| Thanks Kristen,This query was used in msdb of System Databases: select * from dbo.backupmediafamilyonly the .bak files showing in the results like this:C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\VCData2\VCData2_backup_201006022358.bakAnd there is no any transaction log record like .trn. In Maintenance Cleanup Task, the (Delete files older than the following:) was set 1 week(s).I have created Back Up Database Task in maintenance plan for both Full and Transaction Log types for over 60 databases in 8 servers in the same way. So there was no [typo] or [trm] kind of error in the setting. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 12:05:00
|
| "This query was used in msdb of System Databases: select * from dbo.backupmediafamily"I was meaning to use the query from the link I posted which gives more detail, and more control over what you see. As an example it will restrict the output to a specific database name.What you are looking for is the backup file path / filename created at the time the job ran successfully.Glad to hear there is no chance of you having made a mistake, but it doesn't help in finding what has happened to the file. As I said, maybe something has deleted it since, but to know that we need to know the path / name of the file that was created and then whether it is still there, or not. |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2010-06-03 : 12:34:28
|
| Thanks Kristen,I used your script and changed the BS.database_name = N'VCDATA2' for my database, and got all 7 records with Type "D", means "Full". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 13:23:42
|
"I used your script and changed the BS.database_name = N'VCDATA2' for my database, and got all 7 records with Type "D", means "Full"."Do you mean that 1) you restricted to Type "D" in the WHERE clause?or 2) that you only saw type "D" records in the output - i.e. the only thing in your WHERE clause was the database name?If its the second then no Transaction Log Backup was created for this database.Might be worth trying the query for a different database - one that you know has successfully had Transaction backups - just to check that the query is actually outputting history data for transaction backups  If that works OK and thus we conclude that no transaction backup was produced for VCDATA2 database then need to review the maintenance plan (nuke it and have another go as Tara suggests seems a good plan to me!) |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2010-06-03 : 14:00:48
|
| Thanks Kristen and Tara,I know the propose of this query, surely I won’t restrict the type. The ONLY change in the query was replacing the N'MyDatabaseName' with my database name N'VCDATA2'. And I got the result of 7 records with “D” in field “type”. Actually I did delete the maintenance plan and recreate a new one last week. I’ll try that again, if still not working, Probably something wrong with the database, and I will do backup/restore for the database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 15:00:44
|
| "I know the propose of this query, surely I won’t restrict the type."Yeah, well I have no way of knowing that you know that.I'd still recommend running the query on a database name that you know has Transaction log backups, just to double check. Different SQL versions or something that I haven't come across before might mean it isn't working properly for you. |
|
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2010-06-03 : 16:41:51
|
| Hi Tara,The job started to backup the trans log file(.trn) on the next hour after I changed the recovery model from Bulk-logged to Full!Don't know why, can someone explain it?Many thnaks to Tara and Kristen. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-04 : 02:19:24
|
| Maintenance Wizard has a reputation for being "Less than ideal".Sorry, no idea why you didn't get TLog backups, but I'm glad you've got it fixed. |
|
|
|
|
Where is my .trn backup file