locked
how to execute TFS 2010 commands in SQL backup script RRS feed

  • Question

  • Hey,

    We are using tfs 2010. We made a plan for daily SQL backup. I want to execute a tfs command line commands in sql backup script. these commands are not part of system32. TFS command and sql backup script is given below

    Scenario:

    1) I want to detach project collection

    2) SQL backup

    3) attach project collection

     

    C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsConfig.exe Collection /detach /collectionName:collectioname

    C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsConfig.exe Collection /attach /collectionName:COLLECTIONNAME /collectionDB:DBINSTANCE;DBNAME

    this command will detach tfs project collection. 

    How I can execute these command in backup script using xp_cmdshell or something else

     

    DECLARE @name VARCHAR(50) -- database name 

    DECLARE @path VARCHAR(256) -- path for backup files 

    DECLARE @fileName VARCHAR(256) -- filename for backup 

    DECLARE @fileDate VARCHAR(20) -- used for file name

    DECLARE @oldFiles VARCHAR(800)

    -- DECLARE @stopTFS VARCHAR(800)

    -- DECLARE @startTFS VARCHAR(800)

    -- SET @stopTFS ='"C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsServiceControl.exe" quiesce'

    -- EXEC  master..xp_cmdshell @stopTFS, no_output

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    SET @path = 'D:\TFS Backup\TFS-' + @fileDate + '\'

    EXEC master.dbo.xp_create_subdir @path

    DECLARE db_cursor INSENSITIVE CURSOR FOR 

    SELECT name 

    FROM master.dbo.sysdatabases 

    WHERE name NOT IN ('master','model','msdb','tempdb','Tfs_DefaultCollection','Tfs_Network','Tfs_QA','Tfs_Test','ReportServerTempDB') 

    OPEN db_cursor  

    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  

    BEGIN  

           SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

           BACKUP DATABASE @name TO DISK = @fileName 

           FETCH NEXT FROM db_cursor INTO @name  

    END  

    CLOSE db_cursor  

    DEALLOCATE db_cursor

    SET @oldFiles ='Forfiles -p "D:\TFS Backup" -d -8 -c "cmd /c rm -rf @path"'

    EXEC  master..xp_cmdshell @oldFiles, no_output

     

     

    -- SET @startTFS ='"C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsServiceControl.exe" unquiesce'

     

    -- EXEC  master..xp_cmdshell @startTFS, no_output

     

    Please help me to sort out this issue

    Wednesday, September 28, 2011 6:57 AM

Answers

All replies

  • Hi Gh Abbas,

    You need to use SQL Server Integration Services (SSIS).

    There are all components there, for example "maintenance plan" component to implement a full backup (in place of using a cursor) and "execute a process task" component.

    It couild be tricky though.

     


    Sergei
    Thursday, September 29, 2011 10:09 AM
  • Sergei Thanks for reply..

     

    I don't have issue with backup. backup script is working fine. I just want to execute tfs commands in this script. if this process is possible by SSIS, can you guide me how I can pass these tfs commands there.

     

    Thanks in advance.

    Thursday, September 29, 2011 10:14 AM
  • Monday, November 14, 2011 7:13 AM
  • Basically you can do it without SSIS, since probably you don't have a knowledge of it. This can be easily done using SQL Job. In sql job you can execute a command using Operating System (CmdExec).

    http://msdn.microsoft.com/en-us/library/ms190264.aspx

    To enable it (CmdExec):

    -- To allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1
    GO
    -- To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    -- To enable the feature.
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    -- To update the currently configured value for this feature.
    RECONFIGURE
    GO
    

    http://msdn.microsoft.com/en-us/library/ms190693.aspx

    Discussion on permissions: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45799

    Solution:

    It's a 3 step sql job.

    Step 1: Use CmdExec ->  C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsConfig.exe Collection /detach /collectionName:collectioname

    Step 2: Use Transact-SQL -> How I can execute these command in backup script using xp_cmdshell or something else

     

    DECLARE @name VARCHAR(50) -- database name 

    DECLARE @path VARCHAR(256) -- path for backup files 

    DECLARE @fileName VARCHAR(256) -- filename for backup 

    DECLARE @fileDate VARCHAR(20) -- used for file name

    DECLARE @oldFiles VARCHAR(800)

    -- DECLARE @stopTFS VARCHAR(800)

    -- DECLARE @startTFS VARCHAR(800)

    -- SET @stopTFS ='"C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsServiceControl.exe" quiesce'

    -- EXEC  master..xp_cmdshell @stopTFS, no_output

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    SET @path = 'D:\TFS Backup\TFS-' + @fileDate + '\'

    EXEC master.dbo.xp_create_subdir @path

    DECLARE db_cursor INSENSITIVE CURSOR FOR 

    SELECT name 

    FROM master.dbo.sysdatabases 

    WHERE name NOT IN ('master','model','msdb','tempdb','Tfs_DefaultCollection','Tfs_Network','Tfs_QA','Tfs_Test','ReportServerTempDB') 

    OPEN db_cursor  

    FETCH NEXT FROM db_cursor INTO @name  

    WHILE @@FETCH_STATUS = 0  

    BEGIN  

           SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 

           BACKUP DATABASE @name TO DISK = @fileName 

           FETCH NEXT FROM db_cursor INTO @name  

    END  

    CLOSE db_cursor  

    DEALLOCATE db_cursor

    SET @oldFiles ='Forfiles -p "D:\TFS Backup" -d -8 -c "cmd /c rm -rf @path"'

    EXEC  master..xp_cmdshell @oldFiles, no_output

      Step 3:  CmdExec again.

    C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsConfig.exe Collection /attach /collectionName:COLLECTIONNAME /collectionDB:DBINSTANCE;DBNAME

    this command will detach tfs project collection. 

    -- SET @startTFS ='"C:\Program Files\Microsoft Team Foundation Server 2010\Tools\TfsServiceControl.exe" unquiesce'

     

    -- EXEC  master..xp_cmdshell @startTFS, no_output

     

     

     


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog




    Monday, November 14, 2011 10:29 AM
  • Thanks Randy,

    First of all sorry for late reply. I will try your suggestions today. I will let you know if it works for me.

    Thanks

    Tuesday, November 29, 2011 6:54 AM