[SQL Server 2008R2] SSMS - Unable to build maintenance plan for backup of transaction log file

Question

Post reply

[SQL Server 2008R2] SSMS - Unable to build maintenance plan for backup of transaction log file

Scarabee

SSChasing Mays

Points: 622
More actions
March 12, 2014 at 4:47 am

#286703

Hi dudes...
There is a database and a ldf file-working properly so far.
When I try to make a maintenance plan for backup, management studio just finds the mdf (which, by the way, can be backed up).
It just can not find the ldf, which is in the same directory.
Any idea ??
Thank you

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

??

A transaction log backup doesn't need to know the location of the file, it's a backup via SQL Server.

What exactly are you trying to do and what are the exact error messages you are getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CKX SSCrazy Points: 2519 More actions · Answer 2

StephenNL (3/12/2014)
Hi dudes...
There is a database and a ldf file-working properly so far.
When I try to make a maintenance plan for backup, management studio just finds the mdf (which, by the way, can be backed up).
It just can not find the ldf, which is in the same directory.
Any idea ??
Thank you

What task are you using to do the backup within the maintenance plan?

Scarabee SSChasing Mays Points: 622 More actions · Answer 3

No error message...

-> Maintenance plan

-> Right click

-> New maintenance plan

-> Task Backup Database

-> Transactionprotocol

!# Log file of database is missing (though database exists)

!# SQL Server does not display the according log file of the database

😉 Thank you...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

There's no where in the backup database task dialog where the log file is supposed to show up. Where in the dialog do you expect to see the log file (and why)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Scarabee SSChasing Mays Points: 622 More actions · Answer 5

I found the reason why you could not backup log files.

Recovery model of the batabase was simple.

When switching it to complete, you can select the database.

Scarabee SSChasing Mays Points: 622 More actions · Answer 6

When I select Backup Type into Transaction Protocol.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Ah, so it wasn't that the log file wasn't being displayed (which is what your previous posts stated), but that when the Backup Type options is switched Transaction log, the database isn't in the list?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Scarabee SSChasing Mays Points: 622 More actions · Answer 8

In the picture above, I choose transaction log (Transaktionsprotoll in German) and select the database.

But I guess, with simple recovery no log file is needed.

Scarabee SSChasing Mays Points: 622 More actions · Answer 9

Scarabee

SSChasing Mays

Points: 622

March 12, 2014 at 6:40 am

#1696352

Yes, exactly.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

The log file's there. Log backups can't be done which is why you cannot select any database which is in simple recovery model when the backup type is set to Transaction Log.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Scarabee SSChasing Mays Points: 622 More actions · Answer 11

So, better change recovery model of database to complete ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Not necessarily. Simple may be fine if simple recovery meets the app's requirements for recoverability.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Greg Edwards-268690 SSC-Insane Points: 20676 More actions · Answer 13

GilaMonster (3/12/2014)
Not necessarily. Simple may be fine if simple recovery meets the app's requirements for recoverability.

Understanding this, and what it means for recovery, can be a database by database decision sometimes.

Since my data warehouse was only updated once a day, simple (after nightly process) was fine.

SharePoint was Full, as we did not want users to possibly lose data. And users were from around the globe, so updates could occur at all hours.

CKX SSCrazy Points: 2519 More actions · Answer 14

StephenNL (3/12/2014)
So, better change recovery model of database to complete ?

It's important you determine what your applications requirements are and how much data it is acceptable can be lost.

You would typically select a Recovery Model based on this.

If it is unclear to you which Recovery Model you might need, here is a reading starting point...

http://msdn.microsoft.com/en-us/library/ms189275(v=sql.105).aspx