Transactional back is successful but unable to find backup in the folder

Question

Transactional backup job succeeded but the backup TRNs aren't available in the location.
 
We have a very small environment.

Backup plan

--------------
 
Daily - Transactional
 
Weekly- Differential
 
Monthly - Full
 
Recently we came to know that transactional backups aren't happening properly. 18 out of 9 only backed up daily. I have checked the backup logs but it showing as backup successful.
 
I tried to run manually and all 18 DB's got backed up.
 
anyone came across this strange issue before on SQL server 2008 R2?

---

Answer 1

taking  backup on a shared folder or mapped drive?

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 2

Hi,

You can run a script to find where the backup files went if you are using standard SQL integrated backups this should show you where the backups were saved to.

SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'YOURDBNAME'
ORDER BY backup_finish_date DESC

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

We are taking backup on shared folder.

---

Jithin Mathews

Answer 4

I know where the backup goes to..

I have checked the backup history , backup log shows 9 DB'sgot backed up and the job was successful.

Backup job doesn't mention about the remaining backup of 9 DB's.

---

Jithin Mathews

Answer 5

 even i faced the same problem ,

resolution

create mapped drive attached it with sql server and every thing was fine there after

check my blog

http://sqlservr.blog.com/2012/04/24/how-to-add-a-mapped-drive-in-sql-server/

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 6

But we are saving the DB backups in the same server.

is it applicable to that kind of environment??

please correct me if i'm wrong

---

Jithin Mathews

Answer 7

Same thread.

http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/9457ac66-e489-4915-90cb-e1a7973c7c54

Regards
Satheesh

Answer 8

are all the backup kicked from maintenance plan ?

Regards
Satheesh

Answer 9

nope...these are sql server agent jobs ..

---

Jithin Mathews

Answer 10

Initially i posted in wrong forum..

---

Jithin Mathews

Answer 11

I found some strange things now..

Small databases are getting backed up normally but backups with huge size isn't happening...

i think some settings are stopping the transactional job here..

---

Jithin Mathews

Answer 12

Are the schedules added to the job properly ? Do you have enough free space on the server? does the job run history shows if the backup job is executed at all?

Regards
Satheesh

Answer 13

Can you check the recovery model of all the databases and see if you have any set to simple?

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 14

There are 2 possible reasons for such behaviour.

1. Recovery model of those particular databases may be simple.

2. At the point of time there are no transaction for that particular DB's.

Check maintenance plan history there must be more details logged.

(Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker)

Thanks,

Atul Gaikwad.

Answer 15

Option 1 worked ...thanks :)

---

Jithin Mathews