Wednesday, March 24, 2010

SQL Restore Simplified with unknown Backup

This query usefull when u hav a backup that is not known to you.

FROM disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\backup\adw.bak'

This query will give u the file structure in which the mdf and ldf is placed and more importantly the names of the mdf and ldf.

You can then run the below mentioned query to restore the databse in proper file structue as decided by you . I restored it to default folders by ms sql server.
restore database AdventureWorks
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\adw.bak'
with move 'AdventureWorksDW2008_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2008_Data.mdf'
,move 'AdventureWorksDW2008_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2008_Log.ldf'