Wednesday, April 14, 2010

TFS 2008 Database Backup and Restore scripts

These scripts can come handy when performing a migration based upgrade.

Backup Script

BACKUP DATABASE [ReportServer] TO  DISK = N'E:\Backups\ReportServer.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServer-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [ReportServerTempDB] TO  DISK = N'E:\Backups\ReportServerTempDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'ReportServerTempDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsActivityLogging] TO  DISK = N'E:\Backups\TfsActivityLogging.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsActivityLogging-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsBuild] TO  DISK = N'E:\Backups\TfsBuild.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsBuild-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsIntegration] TO  DISK = N'E:\Backups\TfsIntegration.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsIntegration-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsVersionControl] TO  DISK = N'E:\Backups\TfsVersionControl.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsVersionControl-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsWarehouse] TO  DISK = N'E:\Backups\TfsWarehouse.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsWarehouse-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsWorkItemTracking] TO  DISK = N'E:\Backups\TfsWorkItemTracking.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsWorkItemTracking-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [TfsWorkItemTrackingAttachments] TO  DISK = N'E:\Backups\TfsWorkItemTrackingAttachments.bak' WITH NOFORMAT, NOINIT,  NAME = N'TfsWorkItemTrackingAttachments-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [WSS_AdminContent] TO  DISK = N'E:\Backups\WSS_AdminContent.bak' WITH NOFORMAT, NOINIT,  NAME = N'WSS_AdminContent-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [WSS_Config] TO  DISK = N'E:\Backups\WSS_Config.bak' WITH NOFORMAT, NOINIT,  NAME = N'WSS_Config-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [WSS_Content] TO  DISK = N'E:\Backups\WSS_Content.bak' WITH NOFORMAT, NOINIT,  NAME = N'WSS_Content-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Script

RESTORE DATABASE [ReportServer] FROM  DISK = N'E:\Backups\ReportServer.bak' WITH  FILE = 1,  MOVE N'ReportServer' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf',  MOVE N'ReportServer_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServer.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [ReportServerTempDB] FROM  DISK = N'E:\Backups\ReportServerTempDB.bak' WITH  FILE = 1,  MOVE N'ReportServerTempDB' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf',  MOVE N'ReportServerTempDB_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsActivityLogging] FROM  DISK = N'E:\Backups\TfsActivityLogging.bak' WITH  FILE = 1,  MOVE N'TfsActivityLogging' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsActivityLogging.mdf',  MOVE N'TfsActivityLogging_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsActivityLogging_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsBuild] FROM  DISK = N'E:\Backups\TfsBuild.bak' WITH  FILE = 1,  MOVE N'TfsBuild' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsBuild.mdf',  MOVE N'TfsBuild_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsBuild_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsIntegration] FROM  DISK = N'E:\Backups\TfsIntegration.bak' WITH  FILE = 1,  MOVE N'TfsIntegration' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsIntegration.mdf',  MOVE N'TfsIntegration_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsIntegration_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsVersionControl] FROM  DISK = N'E:\Backups\TfsVersionControl.bak' WITH  FILE = 1,  MOVE N'TfsVersionControl' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsVersionControl.mdf',  MOVE N'TfsVersionControl_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsVersionControl_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsWarehouse] FROM  DISK = N'E:\Backups\TfsWarehouse.bak' WITH  FILE = 1,  MOVE N'TfsWarehouse' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWarehouse.mdf',  MOVE N'TfsWarehouse_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWarehouse.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsWorkItemTracking] FROM  DISK = N'E:\Backups\TfsWorkItemTracking.bak' WITH  FILE = 1,  MOVE N'TfsWorkItemTracking' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTracking.mdf',  MOVE N'TfsWorkItemTracking_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTracking.LDF',  MOVE N'sysft_TeamFoundationServer10FullTextCatalog' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTracking.TeamFoundationServer10FullTextCatalog',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [TfsWorkItemTrackingAttachments] FROM  DISK = N'E:\Backups\TfsWorkItemTrackingAttachments.bak' WITH  FILE = 1,  MOVE N'TfsWorkItemTrackingAttachments' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTrackingAttachments.mdf',  MOVE N'TfsWorkItemTrackingAttachments_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TfsWorkItemTrackingAttachments_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [WSS_AdminContent] FROM  DISK = N'E:\Backups\WSS_AdminContent.bak' WITH  FILE = 1,  MOVE N'WSS_AdminContent' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_AdminContent.mdf',  MOVE N'WSS_AdminContent_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_AdminContent_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [WSS_Config] FROM  DISK = N'E:\Backups\WSS_Config.bak' WITH  FILE = 1,  MOVE N'WSS_Config' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Config.mdf',  MOVE N'WSS_Config_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Config_1.LDF',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [WSS_Content] FROM  DISK = N'E:\Backups\WSS_Content.bak' WITH  FILE = 1,  MOVE N'WSS_Content' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Content.mdf',  MOVE N'WSS_Content_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WSS_Content_1.LDF',  NOUNLOAD,  STATS = 10
GO

2 comments:

Shaun said...

You are golden god. Thank you.

Unknown said...

I have upgraded to TFS 2008 from 2005 version.
How to restore the (TFS 2008 upgraded from TFS 2005)Database to new server, Because after installed TFS 2008 in new server database name has changed in tfs 2008, Upgraded TFS server database name is different. Can you please help me in this issue.?