Friday, April 15, 2016

Restore database to another name on the same SQL server

I often need to copy a database into another database on the same SQL server instance. So I take a backup and restore from it into another database. And here is the script:

RESTORE DATABASE [Tfs_tfs] FROM DISK=N'C:\Program Files\Microsoft SQL Server\MSSQL11.NATANSQL\MSSQL\Backup\BackupFile.bak'
WITH
   MOVE 'Tfs_MyCollection' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.NATANSQL\MSSQL\DATA\Tfs_tfs.mdf',
   MOVE 'Tfs_MyCollection_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.NATANSQL\MSSQL\DATA\Tfs_tfs_log.ldf'
GO

Where Tfs_MyCollection is the name of original database,
BackupFile.bak is the backup of it, and
Tfs_tfs is the new database name.

1 comment: