August 6, 2020 at 9:53 pm
You've heard of "the accidental DBA", right? Well, I'm the accidental TFS admin. I have a bit more knowledge of TFS, than the average developer, ergo when the former TFS admin left, I got stuck with it.
But my question is more about SQL Server, than TFS. Our TFS backups haven't worked in over a week. I've stumbled upon (literally, because no one bothered to show it to me) the TFS Management console, where I found that TFS is backing up the SQL Server database to a separate server. (The TFS database is on one server, the backups on another.) However, I have no privileges on the backup server beyond being able to remote to it.
The backups had been going to one hard drive, but it's nearly full. I asked that the share pointing to the drive be changed to point to another drive, with more space. I think it's done, as I'm now seeing .log files in the new drive. But the backups still aren't happening. There's errors like this:
[8/6/2020 8:00:01 AM] [Info] Taking Full Backup. Server: DOHR2SIMTFS009, Database: Tfs_DefaultCollection Filename: \\OURSERVER\TFS Backups\Tfs_DefaultCollection_9860695012869736624F.bak
[8/6/2020 8:00:01 AM] [Info] BACKUP DATABASE [Tfs_DefaultCollection] TO DISK = N'\\OURSERVER\TFS Backups\Tfs_DefaultCollection_9860695012869736624F.bak' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 10
[8/6/2020 8:00:01 AM] [Error]
What would be causing these errors?
Kindest Regards, Rod Connect with me on LinkedIn.
August 6, 2020 at 11:25 pm
that is not an error but a info message - the last entry is an "error" but no description of what it is.
the other messages should be removed from the log by using trace flag 3226 https://www.sqlservercentral.com/blogs/trace-flag-3226-suppress-all-successful-backups-in-sql-server-error-log
August 7, 2020 at 12:37 pm
You're telling me that 'Error' isn't giving you enough information? I'm shocked.
I know you know your stuff, but just in case the obvious has passed you by (I'm an expert at missing the obvious).
Have you tried to run the backup command manually, from within SSMS? And then with the account that normally executes it? There's a chance of more helpful information from within SSMS.
Have you tried backing up the file to another drive (that you know works) and then copying the file across? Just in case it is a permissions/access issue.
Other than that, have you tried a pentagram, black candles and a dead goat?
August 7, 2020 at 1:38 pm
You're telling me that 'Error' isn't giving you enough information? I'm shocked.
I know you know your stuff, but just in case the obvious has passed you by (I'm an expert at missing the obvious).
Have you tried to run the backup command manually, from within SSMS? And then with the account that normally executes it? There's a chance of more helpful information from within SSMS.
Have you tried backing up the file to another drive (that you know works) and then copying the file across? Just in case it is a permissions/access issue.
Other than that, have you tried a pentagram, black candles and a dead goat?
All good questions. The answer to them is no, I haven't tried running the backups manually. First, I don't know where they are. When the former TFS administrator left, he didn't bother to show me how he had it set up. Nor especially how it was defined. I'm assuming its somewhere in the TFS Manager interface that I only recently discovered after poking around for a hour or so. Guess I'll go back into it and try to find where in heck the backup plan is and what it is.
Kindest Regards, Rod Connect with me on LinkedIn.
August 7, 2020 at 4:17 pm
Can you log into the server via SSMS? If so, I would try running the backup command in the INFO message manually and see what error you get back.
If you do not have access to the server over SSMS, then you are going to have a tougher time. I would personally look to find someone who does have access and pass the troubleshooting over to them.
Does the drive \\OURSERVER\TFS Backups have enough free space to do a FULL backup of your database?
Also, if you can get access to view the query for the job, I'd personally remove the "STATS = 10" portion of the query. You are running this as a job, so showing the status of the backup to nobody is not really needed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 7, 2020 at 7:58 pm
I do have access to the SQL Server that's used by TFS. I got onto it, then got into SQL Jobs (it's a SQL Server 2012 server). There are 6 jobs there. I looked at all of them. None of them have anything to do with backing up the TFS databases. There are other databases on that server, which those jobs are written around.
It really frustrates me. My guess is that the backup command is issued through the TFS Administration console, but I don't know where it is in TFS Administrator console.
Kindest Regards, Rod Connect with me on LinkedIn.
August 7, 2020 at 9:06 pm
As you have access to the server, what happens if you run the TSQL indicated in the second INFO message you posted above:
BACKUP DATABASE [Tfs_DefaultCollection] TO DISK = N'\\OURSERVER\TFS Backups\Tfs_DefaultCollection_9860695012869736624F.bak' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 10
That is the backup command being run, so you could use that to see what error SSMS is giving you (if any).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 8, 2020 at 8:45 am
I've no experience with TFS (fortunately?), but have you had a poke around in Maintenance Plans and SSIS? Has it done something there to handle backups, which might give you a clue?
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
August 9, 2020 at 2:36 pm
Good question. I have limited experience with Maintenance Plans, having used them in my previous job to setup backup jobs. I don't really know SSIS. I took a Pluralsight course on SSIS about 4 years ago, because at that time I was told I'd be using it at work. Well, that never happened. My boss has directed me into other areas so that training has gone by the wayside.
Kindest Regards, Rod Connect with me on LinkedIn.
August 9, 2020 at 4:28 pm
What is the version of TFS?
Have you looked at this documentation: https://docs.microsoft.com/en-us/azure/devops/server/upgrade/upgrade-2013/backup-and-restore-data?view=tfs-2015
It appears to me that there is a separate tool you need to run on the TFS server to setup and manage those backups. Here is another link: https://docs.microsoft.com/en-us/azure/devops/server/admin/backup/back-up-restore?view=tfs-2018
Looking in SQL Server to find the backups isn't going to work for TFS - which manages those backups outside of SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply