Monday 30 March 2015

Restore Database using T-SQL

Generate New Database from blank database bak file
           
DECLARE @Query VARCHAR(MAX)

DECLARE @mdfPath VARCHAR(500)

DECLARE @ldfPath VARCHAR(500)

--RESTORE FILELISTONLY FROM DISK='C:\Program Files\Microsoft SQL  Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\oldDB.bak'

-- Get Logical names to restore database.

SET @mdfPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\newDb.mdf'

SET @ldfPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ newDb.ldf'

 RESTORE DATABASE newDB FROM DISK='C:\Program Files\Microsoft SQL Server\
                                                            MSSQL11.MSSQLSERVER\MSSQL\Backup\oldDB.bak'
 WITH

 MOVE 'oldDB' TO @mdfPath,

 MOVE 'oldDB_log' TO @ldfPath