How can I restore all my Tfs Databases from script


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!

 

Comments (0)

Skip to main content