SQL 2005 Server, cant back all DB's T-Logs up 1

[AndyFreeman]

I have stumbled across a little proble.

I have been setting up some maintenance wizards to backup all of our DB transaction logs. However it will not show me all of the databases present on the server. It only shows me some of the User DB’s.


I have had a look around the only difference I can see is that on the DB’s that I am able to backup in the “Database Properties”>”Permissions” Window there is nothing in there.

For the databases I am not able to see there is either a guest account or public. It will not let me remove these users although I am logged on as SA.

Can you offer any advice please

 

[ptheriault]

Andy,
Are all the databases set to FULL Recovery?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AndyFreeman]

Wonderful, that was it.

Amazing when ur close to a problem how its always the small things that sat in front of you that you dont see.

Thanks so much

Paul

 

[ptheriault]

No problem
I'm glad I could help.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AndyFreeman]

No sure if this error will tell you much but i am receiving this error whenever i run the DB maint Task to backup the T-log for just a single Db


Message
Executed as user: LAUW03EXCH02\SYSTEM. ....00.3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:56:49 PM Error: 2007-03-05 14:56:49.56 Code: 0xC0010018 Source: Back Up Database Task Description: Error loading a task. The contact information for the task is "Back Up Database Task; Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;

http://www.microsoft.com/sql/support/default.asp;1".

This happens when loading a task fails. End Error Progress: 2007-03-05 14:56:49.85 Source: {5443795A-C960-416D-98B3-9B1A409DB85B} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2007-03-05 14:56:50.21 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Backups\T".: 100% complete End Progress Error: 2007-03-05 14:56:50.23 Code: 0xC002F210 Source: Ba... The package execution fa... The step failed.

 

[ptheriault]

Does that account that the SQL Agent is running under have permissions to create files on the server?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AndyFreeman]

I presume so as the same account would be used to create the Full DB backups on that server also

I have 2 Plans
Plan 1/ Does full Db backup every 12 hours
Plan 2/ Every 1 hour backups up T-Logs of same DB's

 

[ptheriault]

Ok, I just noticed that it's executing as system so permissions shouldn't be a problem. So plan 1 executes without any errors but plan 2 fails. Does plan 1 use subdirectories?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AndyFreeman]

Plan 1 I Specify the DB's to backup and uses sub directories

I have tried plan 2 on both subsirs and not and also on specified db's and all user db's

Dont seem to be able to get any combination to work unfortunately

 

[ptheriault]

Andy,
I've never seen that happen before. I'm not sure I could offer you any advice on how to correct it. Sorry.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AndyFreeman]

Ok no probs, thanks for all your help.

I think it has something to do with Db owners etc the only DB's i am having a problem with is ones migrated across from our old SQL 2000 Server

I am having a good look around now