Why aren't the backups working?

  • Rod at work

    SSC-Dedicated

    Points: 33412

    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?

    • This topic was modified 1 month, 2 weeks ago by  Rod at work. Reason: corrected grammer

    Kindest Regards, Rod Connect with me on LinkedIn.

  • frederico_fonseca

    SSChampion

    Points: 14747

    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

  • BrainDonor

    SSCoach

    Points: 19234

    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?

    Steve Hall
    Linkedin
    Blog Site

  • Rod at work

    SSC-Dedicated

    Points: 33412

    BrainDonor wrote:

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23165

    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.

  • Rod at work

    SSC-Dedicated

    Points: 33412

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23165

    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).

  • david.edwards 76768

    Ten Centuries

    Points: 1069

    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?

  • Rod at work

    SSC-Dedicated

    Points: 33412

    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.

  • Jeffrey Williams

    SSC Guru

    Points: 88654

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Rod at work

    SSC-Dedicated

    Points: 33412

    We're using TFS 2015.

    Thank you for linking to that documentation! I'm going to check it out.

    Kindest Regards, Rod Connect with me on LinkedIn.

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

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