How can I restore all my Tfs Databases from script

  • Article

In this example, I have used the Tfs2008 databases as the list is fairly long.  If you so desire to re-purpose this script for Tfs2010,2012,2013; simply edit the INSERT statement to resemble (keeping in mind that in my example I only have on collection DB:

 --Create temp table to store the Tfs Database names
 CREATE TABLE #tmp (TfsDBName varchar(MAX)) 
 INSERT INTO #tmp VALUES ('Tfs_Configuration')
 INSERT INTO #tmp VALUES ('Tfs_DefaultCollection')
 INSERT INTO #tmp VALUES ('TFS_Warehouse')

Please pay attention to the path and name variables used.  You must edit them to match your own environment.

For Tfs2008 Databases:

  --START SCRIPT

  DECLARE @BackUpURL VARCHAR(MAX)
 DECLARE @DBBackUpFile VARCHAR(MAX)
 DECLARE @DataRestoreURL VARCHAR(MAX)
 DECLARE @LogRestoreURL VARCHAR(MAX)
 DECLARE @DBRestoreDataFile VARCHAR(MAX)
 DECLARE @DBRestoreLogFile VARCHAR(MAX)
 DECLARE @DBName VARCHAR(MAX)
 DECLARE @DBLogName VARCHAR(MAX)
 DECLARE @CompatLevel VARCHAR(MAX)
 
 --Set the URL Variable bellow to the root of your DB backup folder
 SET @BackUpURL = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\BackUp\'
 SET @DataRestoreURL = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
 SET @LogRestoreURL = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\'
 
 --Create temp table to store the Tfs Database names
 CREATE TABLE #tmp (TfsDBName varchar(MAX)) 
 INSERT INTO #tmp VALUES ('TfsActivityLogging')
 INSERT INTO #tmp VALUES ('TfsBuild')
 INSERT INTO #tmp VALUES ('TfsIntegration')
 INSERT INTO #tmp VALUES ('TfsVersionControl')
 INSERT INTO #tmp VALUES ('TFSWarehouse')
 INSERT INTO #tmp VALUES ('TfsWorkItemTracking')
 INSERT INTO #tmp VALUES ('TfsWorkItemTrackingAttachments')
 
 DECLARE db_cursor CURSOR FOR 
 SELECT TfsDBName FROM #tmp
 OPEN db_cursor 
 FETCH NEXT FROM db_cursor INTO @DBName 
 WHILE @@FETCH_STATUS = 0 
 
 BEGIN 
 
 SET @DBLogName = @DBName + N'_log'
 SET @DBBackUpFile = @BackUpURL + @DBName + N'.BAK'
 SET @DBRestoreDataFile = @DataRestoreURL + @DBName + '.MDF'
 SET @DBRestoreLogFile = @LogRestoreURL + @DBLogName + '.LDF'
 
 RESTORE DATABASE @DBName FROM 
 DISK = @DBBackUpFile 
 WITH FILE = 1, 
 MOVE @DBName TO @DBRestoreDataFile, 
 MOVE @DBLogName TO @DBRestoreLogFile, 
 NOUNLOAD, REPLACE, STATS = 10
 
 SET @CompatLevel = N'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 100 ' --Update the Database compatibility level to SQL 2008
 + N'ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL WITH NO_WAIT ' --Set the Database to Full Recovery
 + N'ALTER DATABASE [' + @DBName + '] COLLATE SQL_Latin1_General_CP1_CI_AS ' --Set Database Collation
 EXEC(@CompatLevel)
 
 FETCH NEXT FROM db_cursor INTO @DBName 
 END
 
 CLOSE db_cursor
 DEALLOCATE db_cursor
 DROP TABLE #tmp
 

 --END SCRIPT

Hope this helps,cheers!