Creating an SQL Server database from a backup file

If you've ever created a backup of an SQL Server database you may have experienced difficulties in restoring or deploying the backup to another server. In many cases the backup will need to be used as a way of deployment to a different server, but this causes an issue if the original database isn't already attached to the target server. A database can be created from a backup file using a simple SQL query.

Within Microsoft SQL Server Management Studio (2005 or 2008), login to the target server with administrative privileges and open a new query. This can be done by going to the menu path: File > New > Query with Current Connection.

Using the following SQL query, include your database name, backup file name and location where indicated.

FROM DISK = 'Full Physical Path 0f Database Backup File Folder Location Backupfile.BAK'
MOVE 'NameOfDatabase_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NameOfDatabase_Data.MDF',
MOVE 'NameOfDatabase_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NameOfDatabase_Log.LDF '

Once the SQL query is executed the database will then be available.

Return to all articles