Search code examples
sqlsql-serversql-server-2008t-sqldatabase-backups

Can you query a backup device?

I would like to get information from a backup of a server (SQL 2008 R2). Do I have to restore the database backup to do this? Is there any way to query the backup?

Solution

  • No, you can't query your backup files. You can always restore the database using the WITH REPLACE option to restore it under a different name.

    Let's say you have a production DB and you want to restore it as a test database.

    You can use WITH REPLACE option to restore TestDB from a .bak file containing a ProductionDB. I also use WITH MOVE option to replace the .mdf and .ldf files:

    USE [master]

RESTORE DATABASE [TestDB] FROM  DISK = N'C:\ProductionDB.bak' 
WITH MOVE N'ProductionDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf',  
MOVE N'ProductionDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf', 
REPLACE

GO