Creating a backup in Team Foundation Server 2010 using the Power Tools

over the last few years the product team has been putting their finishing touches on a backup module for the team foundation server administration console. why you might ask do you need another way to backup? surely you can just backup the bits?

well, you could, but as tfs has a lot of moving parts it can get very complicated to creating a backup .

1. required permissions

2. identify databases

3. create tables in databases

4. create a stored procedure for marking tables

5. create a stored procedure for marking all tables at once

6. create a stored procedure to automatically mark tables

7. create a scheduled job to run the table-marking procedure

8. create a maintenance plan for full backups

9. create a maintenance plan for differential backups

10. create a maintenance plan for transaction backups

11. back up additional lab management components

-from “back up team foundation server” on msdn

there are a heck of a lot of databases that, depending on your environment, might be spread over your entire network.

figure: deployment topologies (where is my data?) from msdn

so, how is this problem solved. well the tfs team have create a tool to create all of the backups and all of the job as well as managing the backup location for you. this sounds fantastic, but how about in practice.

was it really that easy? well….not really…here is the extra stuff i found out:

• your account must own the share
  owning the folder does not cut it (see error #1- tf254027).
• sql must be running under a domain account or network service
  sql must also have permission to the share, and the validation will get confused if you use “localsystem” instead of network service or a domain account (see error #2- tf254027)
• the account running sql must have permission to create spn’s
  the account that is used for sql must be able to both see and create service principal names in active directory (see error #3: terminating your tfs server).

once you learn how to google without keywords and read your servers mind you will have a nice backup system going…

error #1- tf254027

i initially got an error because the accounts did not really have full control over the target location. this is a problem with the share. although i have full permission for \\fileserver1\share\tfsbackups it is just a folder under the \\fileserver1\share\ location and i do not have permission to change the sharing settings there.

figure: tf254027 is caused by permission issues

[info   @16:36:34.342] granting account root_company\tfssqlbox$ permission on folder \\fileserver1\share\tfsbackups
[info   @16:36:34.348] system.unauthorizedaccessexception: attempted to perform an unauthorized operation.
   at system.security.accesscontrol.win32.setsecurityinfo(resourcetype type, string name, safehandle handle, securityinfos securityinformation, securityidentifier owner, securityidentifier group, genericacl sacl, genericacl dacl)
   at system.security.accesscontrol.nativeobjectsecurity.persist(string name, safehandle handle, accesscontrolsections includesections, object exceptioncontext)
   at system.security.accesscontrol.filesystemsecurity.persist(string fullpath)
   at microsoft.teamfoundation.powertools.admin.helpers.filehelper.grantfolderpermission(string account, string path)
[info   @16:36:34.350] granting account root_company\tfs.services permission on folder \\fileserver1\share\tfsbackups
[info   @16:36:34.352] system.unauthorizedaccessexception: attempted to perform an unauthorized operation.
   at system.security.accesscontrol.win32.setsecurityinfo(resourcetype type, string name, safehandle handle, securityinfos securityinformation, securityidentifier owner, securityidentifier group, genericacl sacl, genericacl dacl)
   at system.security.accesscontrol.nativeobjectsecurity.persist(string name, safehandle handle, accesscontrolsections includesections, object exceptioncontext)
   at system.security.accesscontrol.filesystemsecurity.persist(string fullpath)
   at microsoft.teamfoundation.powertools.admin.helpers.filehelper.grantfolderpermission(string account, string path)
[error  @16:36:34.352] granting permission to account root_company\tfssqlbox$ on path \\fileserver1\share\tfsbackups failed

figure: the log files get to the root of the problem, but not the reason

after much messing around i have found that you can’t use a sub-folder of a share that you do not have permission for. you require permission to the share itself to apply permissions.

error #2- tf254027

lets try this again with a share that we control. i will create a backup share on the tfs server and at least then i control then permissions.

figure: the next error looks the same, but it is subtly different

[info   @18:12:05.813] "verify: grant backup plan permissions\root\verifybackuppathpermissionsgrantedsuccessfully(verifybackuppathpermissionsgrantedsuccessfully): exiting verification with state completed and result success"
[info   @18:12:05.813] verify: grant backup plan permissions\root\verifydummybackupcreation(verifytestbackupcreatedsuccessfully): starting verification
[info   @18:12:05.813] verify test backup created successfully
[info   @18:12:05.813] starting creating backup test validation
[error  @18:12:06.132] microsoft.sqlserver.management.smo.failedoperationexception: backup failed for server 'sqlserver1'.  ---> microsoft.sqlserver.management.common.executionfailureexception: an exception occurred while executing a transact-sql statement or batch. ---> system.data.sqlclient.sqlexception: cannot open backup device '\\tfsserver1\tfsbackup\temp_20111104111205.bak'. operating system error 5(access is denied.).
backup database is terminating abnormally.
at microsoft.sqlserver.management.common.connectionmanager.executetsql(executetsqlaction action, object execobject, dataset filldataset, boolean catchexception)
at microsoft.sqlserver.management.common.serverconnection.executenonquery(string sqlcommand, executiontypes executiontype)
--- end of inner exception stack trace ---
at microsoft.sqlserver.management.common.serverconnection.executenonquery(string sqlcommand, executiontypes executiontype)
at microsoft.sqlserver.management.common.serverconnection.executenonquery(stringcollection sqlcommands, executiontypes executiontype)
at microsoft.sqlserver.management.smo.executionmanager.executenonquery(stringcollection queries)
at microsoft.sqlserver.management.smo.backuprestorebase.executesql(server server, stringcollection queries)
at microsoft.sqlserver.management.smo.backup.sqlbackup(server srv)
--- end of inner exception stack trace ---
at microsoft.sqlserver.management.smo.backup.sqlbackup(server srv)
at microsoft.teamfoundation.powertools.admin.helpers.backupfactory.testbackupcreation(string path)
[error  @18:12:06.184] !verify error!: account root_comapny\martin.hinshelwood failed to create backups using path \\tfsserver1\tfsbackup
[info   @18:12:06.184] "verify: grant backup plan permissions\root\verifydummybackupcreation(verifytestbackupcreatedsuccessfully): exiting verification with state completed and result error"
[info   @18:12:06.184] !verify result!: 5 completed, 0 skipped: 4 success, 1 errors, 0 warnings
[info   @18:12:06.197] verify: backup tasks verifications(vcontainer): starting verification
[info   @18:12:06.197] a generic container node that does not contribute to results
[info   @18:12:06.197] "verify: backup tasks verifications(vcontainer): exiting verification with state ignore and result ignore"
[info   @18:12:06.197] verify: backup tasks verifications\root(vcontainer): starting verification
[info   @18:12:06.197] a generic container node that does not contribute to results
[info   @18:12:06.197] "verify: backup tasks verifications\root(vcontainer): exiting verification with state ignore and result ignore"
[info   @18:12:06.197] verify: backup tasks verifications\root\verifydummybackupcreation(verifytestbackupcreatedsuccessfully): starting verification
[info   @18:12:06.197] verify test backup created successfully
[info   @18:12:06.197] starting creating backup test validation
[error  @18:12:06.389] microsoft.sqlserver.management.smo.failedoperationexception: backup failed for server sqlserver1'.  ---> microsoft.sqlserver.management.common.executionfailureexception: an exception occurred while executing a transact-sql statement or batch. ---> system.data.sqlclient.sqlexception: cannot open backup device '\\tfsserver1\tfsbackup\temp_20111104111206.bak'. operating system error 5(access is denied.).
backup database is terminating abnormally.
at microsoft.sqlserver.management.common.connectionmanager.executetsql(executetsqlaction action, object execobject, dataset filldataset, boolean catchexception)
at microsoft.sqlserver.management.common.serverconnection.executenonquery(string sqlcommand, executiontypes executiontype)
--- end of inner exception stack trace ---
at microsoft.sqlserver.management.common.serverconnection.executenonquery(string sqlcommand, executiontypes executiontype)
at microsoft.sqlserver.management.common.serverconnection.executenonquery(stringcollection sqlcommands, executiontypes executiontype)
at microsoft.sqlserver.management.smo.executionmanager.executenonquery(stringcollection queries)
at microsoft.sqlserver.management.smo.backuprestorebase.executesql(server server, stringcollection queries)
at microsoft.sqlserver.management.smo.backup.sqlbackup(server srv)
--- end of inner exception stack trace ---
at microsoft.sqlserver.management.smo.backup.sqlbackup(server srv)
at microsoft.teamfoundation.powertools.admin.helpers.backupfactory.testbackupcreation(string path)

figure: this time the error is lying and is from sql not locally as it implies

it looks like the problem is that sql server can’t write to that folder, but i can and the machine account can. lets try this from the sql server itself, and with a native backup.

figure: sql server can’t write to that location

dam… so even a native sql backup can’t write to this location.

title: microsoft sql server management studio
------------------------------

backup failed for server 'sqlserver1'.  (microsoft.sqlserver.smoextended)

for help, click: http://go.microsoft.com/fwlink?prodname=microsoft+sql+server&prodver=10.50.2500.0+((kj_pcu_main).110617-0038+)&evtsrc=microsoft.sqlserver.management.smo.exceptiontemplates.failedoperationexceptiontext&evtid=backup+server&linkid=20476

------------------------------
additional information:

system.data.sqlclient.sqlerror: cannot open backup device '\\tfsserver1\tfsbackup\moo.bak'. operating system error 5(access is denied.). (microsoft.sqlserver.smo)

for help, click: http://go.microsoft.com/fwlink?prodname=microsoft+sql+server&prodver=10.50.2500.0+((kj_pcu_main).110617-0038+)&linkid=20476

------------------------------
buttons:

ok
------------------------------

figure: sql server errors suck even more

as it turns out, sql server is running under “localserivce” which is not authenticating against our share. so we need to change the service that tfs runs under.

error #3: terminating your tfs server

as we should always use the sql server configuration manager to change these things i fired it up and since i already have a domain account for running tfs under i decided to use that one.

figure: this is easy

when you apply it will ask you to restart sql, but it should be all complete. lets check tfs and make sure that everything is running…

figure: omg! what just happened!

oh shit: i think i just broke tfs. why can’t tfs connect? lets try the sql management studio and see.

figure: what is a sspi?

this does not look good…

after i have hastily changed the service account back to the original value and made use that his fixed tfs i wanted to also figure out why it broke. usually i would just ask shad (one of my extremely technical colleagues) but alas he is on his honeymoon. some googling turned up an spn issue. the account that sql runs under must be able to both read and write service principal names for itself in active directory. this can be set, but only be a domain admin.

• dynamically set spn’s for sql service accounts

so lets go with network service instead. if we change the account that sql server runs under to “network service” then i can add permission for “root_company\sqlserver1$” to my share and get it working. yes, servers have ad accounts as well.