Grant Holliday

Often I find myself restoring the 11 or so TFS databases. Usually I’m doing it for one of four things:

1. Restoring in a VPC for development / testing
2. Restoring to a second “development” server so that people can try things before they do them in production
3. A disaster recovery rebuild (or exercise), or
4. Moving the Data Tier to another server as part of an upgrade

If you find yourself in any of these situations, these docs are the most useful:

• How to: Restore Team Foundation Server Data
• How to: Move Your Team Foundation Server from One Hardware Configuration to Another
• Changing TFS instance GUID (especially important if you are restoring a copy of TFS while the original instance is still running)

Usually I have a directory full of *.bak SQL Backup files and in the past I’ve either gone through the ‘Restore database’ wizard, or hand-written the SQL restore script. This week I invested the time to build a batch script that makes it a whole lot easier and repeatable.

@echo off

@echo — This script restores all *.bak files in a given directory to a given server.

IF x==%4x (

@echo.

@echo Usage:

@echo RestoreDBs.cmd [backup directory] [server\instance] [mdb dir] [ldf dir]

@echo.

@echo Example:

@echo RestoreDBs.cmd “C:\SQLBackups” “DBSERVER\DBINSTANCE” “X:\SQL2005\MSSQL.1\MSSQL\Data” “Y:\SQL2005\MSSQL.1\MSSQL\Logs”

@echo.

GOTO :EOF

)

:: %~3 - expands %3 removing any surrounding quotes (”)

set BACKUPDIRECTORY=%1

set SERVER=%2

set DESTINATION_MDB=%~3

set DESTINATION_LDF=%~4

PUSHD %BACKUPDIRECTORY%

FOR %%A in (*.bak) do CALL :restoreDB %%A POPD GOTO :EOF

:restoreDB

:: %~nx1 - expands %1 to a file name and extension only

set DBFILENAME=%~nx1

::%~f1 - expands %1 to a fully qualified path name

set DBFULLFILENAME=%~f1

:: Trim the last 24 characters off the filename which represent ‘_backup_YYYYMMDDHHmm.bak’

set DBNAME=%DBFILENAME:~0,-24%

echo.

echo — Restoring %DBNAME%

echo — from %DBFULLFILENAME%

echo.

set SQLCMD=RESTORE DATABASE [%DBNAME%] FROM DISK = N’%~f1′ WITH FILE = 1, MOVE N’%DBNAME%’ TO N’%DESTINATION_MDB%\%DBNAME%.mdf’, MOVE N’%DBNAME%_log’ TO N’%DESTINATION_LDF%\%DBNAME%_log.LDF’, NOUNLOAD, REPLACE, STATS = 10

::isql -S %SERVER% -E -d master -Q “RESTORE DATABASE [%DBNAME%] FROM DISK = N’%DBFULLFILENAME%’ WITH FILE = 1, MOVE N’%DBNAME%’ TO N’%DESTINATION_MDB%\%DBNAME%.mdf’, MOVE N’%DBNAME%_log’ TO N’%DESTINATION_LDF%\%DBNAME%_log.LDF’, NOUNLOAD, REPLACE, STATS = 10″

echo %SQLCMD%

echo GO

echo.

echo.

GOTO :EOF

This relies on the backup files having the default names given to them by a SQL Maintenance Plan. i.e. DatabaseName_backup_YYYYMMDDHHmm.bak

You can use it in two ways:

• Pipe the output to a SQL file and load it in SQL Server Management Studio. e.g. RestoreDBs.cmd …. > restorescript.sql
• Uncomment the line that starts with “isql” and run it directly from the command line.

Copy & paste from above, or download the script here: RestoreTFSDBs.cmd