Question:
After SQL database’s backup has been performed, how can I recover the generated .BAK file?
Solution:
To do so, follow these procedures:
1. Open Management Studio.
2. Click New Query option, or go to File-New-Query with current connection.
3. On query’s window, type the following:
RESTORE FILELISTONLY
FROM DISK = ‘c:\backup\testsql.bak’
GO
4. Click Execute; as a result, .MDF and .LDF files will be displayed, similarly to the image below, and with each file’s respective logical name:
5. Create a new query by repeating step 2.
6. Type the following command:
RESTORE DATABASE test
FROM DISK = ‘c:\backup\testsql.bak’
WITH MOVE ‘teste’ TO ‘c:\backup\test.MDF’, MOVE ‘test_log’ TO ‘c:\backup\test_Log.LDF’
NOTE: The names test.MDF and test_log.LDF were chosen for no specific reason. You can choose any other name for these files.
7. Click Execute; the messages regarding your backup’s recovery will be displayed:
8. Check the folder c:\backup for the recovered files:
9. Click Refresh in Object Explorer, and then expand the objects in the database; you will be able to visualize the recovered Test file, according to the figure below:
This procedure has been performed by using Microsoft SQL Server 2008 R2 Management Studio.
