SQL Backup Problem

[GRitchie]

CAPITA are as much use as a chocolate fireguard so I thought here might be helpful:

 

We've recently updated our VEEAM server and it had caused a few errors. I got in touch with VEEAM and they pointed me in the direction of the fixes, one of which was the need to set permissions for SQL.

 

The error on the MIS server we're receiving on the backup is:

[i]"Unable to truncate SQL server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate transaction logs for SQL instances: SIMS2008. Possible reasons: lack of permissions, or transaction log corruption."

[/i]

VEEAMs fix is:

[i]Error: OpenFromInitializationString failed. [Login failed for user 'DOMAIN\user'.]  
Solution: give DOMAIN\user permissions  on SQL instance and add db_backupoperator role for all FULL and BULK databases, or give it a sysadmin role.
[/i]

 

The issue lies when I go to open that database to set the permissions, the 'file does not exist'.

The worrying thing is, we no longer use the 'SIMS2008' database, so it may very well have been removed!

 

Any ideas on how to fix this?

[glen_j]

the sims2008 is the instance not the database you should be able connect to the instance via slq management and make the changes.

[GRitchie]

http://i.gyazo.com/7e5777d7f2dfaa3d05c04e67c669d946.png

 

Oh, sorry - regardless, this is the error when I try and connect...

[kmount]

Try HOSTNAME\SIMS2008

[GRitchie]

Nope - still no luck!

[Atomic_Zombie]

In SQL configuration manager, is Named Pipes enabled?

[GRitchie]

Sorry, not overly familiar with SQL server. Could you point me in the right direction?

[aleach2]

@GRitchie What user account are you using to backup the SIMS server? We had the same issue. We just disabled Truncate logs in the application-aware processing section of the Veeam job. Probably not the best option. Seeing this post though has made me look at the issue again. I have noticed I was using our backup service account to backup our MIS server which didn't have access to the SIMS databases. I have added our Administrator account to the Guest OS credentials section and I will monitor if I get the error again tonight when the job runs.

[GRitchie]

We use the network Administrator account to run the VEEAM backup. I did see about disabling Truncate logs, but was a bit skeptical about it myself!

[Cache]

Has the instance been disabled by any chance?

 

Veeam has started trying to truncate our old SQL 2005 instance which is disabled (should really uninstall it) sometime after i installed Veeam 8 Update 2.

[GRitchie]

Could be possible.

 

 

I disabled truncate logs for over the weekend and that's now cleared the error. But is it good to be suppressing errors like this?

[vikpaw]

It depends on whether you even need to truncate logs. Do you need to do a transaction log backup? Look at some related threads. Generally speaking your backup mode should be Simple, so there should be no log file change, therefore no need to truncate.

 

With regards to your login issue, are you trying a remote connection from your desktop client? Try doing it straight on the server. I've had a few boxes where you can't get remote access after Symantec AV had issues on the box.

[aleach2]

@GRitchie What user account are you using to backup the SIMS server? We had the same issue. We just disabled Truncate logs in the application-aware processing section of the Veeam job. Probably not the best option. Seeing this post though has made me look at the issue again. I have noticed I was using our backup service account to backup our MIS server which didn't have access to the SIMS databases. I have added our Administrator account to the Guest OS credentials section and I will monitor if I get the error again tonight when the job runs.

@GRitchie I have now enabled truncate logs again and changed the user account on Veeam to our domain admin account and this works fin now with no errors. I guess our backup service account didn't have access to truncate logs.

[Steve21]

In Veeam8 they changed it from using SYSTEM account to truncate to the account you're running the backup under, so add your Veeam service account as admin on SQL if you want that without running domain admin etc.

 

Steve

[GRitchie]

Hm, that would imply OUR domain admin doesn't have permission to the SQL then?

[Steve21]

Hm, that would imply OUR domain admin doesn't have permission to the SQL then?

 

Was it a Capita install as if so quite often they didn't in our old feeders, as their logic was you don't need access more than Solus etc. Always had to add it using the SA accounts etc.

 

But I'd still check in the SQL configuration manager etc and see if it's actually running/setup

 

Steve

[GRitchie]

I can only image in it was.

We were given SA account details for the current database, but I'm unsure about the older one (with the issues).

 

How do I check config manager to see if it's set up if I can't get into it then?

[Steve21]

Config manager, not management studio

 

Under services should show what's running server name/instance wise

 

Named Pipes/TCP-IP Settings etc.

 

Steve

[GRitchie]

So we disabled all the items here and the backup still errors