Restoring a Database from .Bak File on Another Machine

Restoring a database from .bak file on another machine

You could use something like the following script. It restores a database from the filesystem, and it overwrites the existing database with the name of "MyDB", moving the files to new locations of your choice in the process.

RESTORE DATABASE
    MyDB
FROM DISK = '\\MyShare\MyBackup.bak'
WITH 
    MOVE 'DataFile' TO 'D:\myNewDBLocation\DataFile.mdf',
    MOVE 'LogFile' TO 'E:\\myNewDBLocation\LogFile.ldf'
, REPLACE

You can find out the name of the llogical files (in the above, those are called DataFile and LogFile by running the following:

RESTORE FILELISTONLY 
FROM DISK = '\\MyShare\MyBackup.bak'

Additional information about various options and parameters:

RESTORE (Transact-SQL)

Restore database using a bak file from another computer

I do it manually. First, find out where your new server keeps it's database (mdf) and log (ldf) files

SELECT name, physical_name FROM sys.master_files

Then, find out what the logical names of those files are in your backup

restore FILELISTONLY FROM DISK='c:\dir\file.bak'

Finally, use combine that info to do the restore:

RESTORE DATABASE DataBaseToCreate
    FROM DISK = 'c:\dir\file.bak' 
    WITH REPLACE, 
    MOVE 'DBLogicalName' TO  'C:\DB_DIR\DataBaseToCreate.mdf',
    MOVE 'LogLogicalName' TO  'C:\LOG_DIR\DatabaseToCreate.ldf'

How to restore to a different database in SQL Server?

You can create a new db then use the "Restore Wizard" enabling the Overwrite option or:

View the contents of the backup file:

RESTORE FILELISTONLY FROM DISK='c:\your.bak'

note the logical names of the .mdf & .ldf from the results, then:

RESTORE DATABASE MyTempCopy FROM DISK='c:\your.bak'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:\MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:\MyTempCopy_log.ldf'

This will create the database MyTempCopy with the contents of your.bak.

(Don't create the MyTempCopy, it's created during the restore)

---

Example (restores a backup of a db called 'creditline' to 'MyTempCopy'):

RESTORE FILELISTONLY FROM DISK='e:\mssql\backup\creditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:\mssql\backup\creditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:\mssql\MyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:\mssql\MyTempCopy_log.ldf'

>RESTORE DATABASE successfully processed 186 pages in 0.010 seconds (144.970 MB/sec).

Restore .bak file to remote database

In the context of this answer - remote refers to your machine, local is the database server.

---

Restore from local filesystem

Copy the backup file to the local filesystem, and restore directly from this copy.

Prerequisites

• Copy test.bak to C:\test.bak on the server

Syntax

RESTORE DATABASE TESTPROJECT FROM DISK = N'C:\test.bak';

---

Restore from remote filesystem

Alternatively you can restore from the remote backup file using UNC syntax. I typically don't use this option, but it is useful if there won't be enough disk space on local filesystem for both the backup file and the restored database.

The success of this option depends on some variables - permissions on the remote filesystem assigned to the database service account, network health, and others.

Prerequisites

• Remote machine name is remotemachine
• Backup located on remote at 'C:\test.bak'
• Database service account has access to the remote administrator share C$

Syntax

RESTORE DATABASE TESTPROJECT FROM DISK = N'\\remotemachine\c$\test.bak';

Cannot restore a database with a .bak file due to another SQL server version

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version (SQL Server 2008 R2 - 10.50.1600) of SQL Server down to an older version (2008 - v10.00.1600) - the internal file structures are just too different to support backwards compatibility.

You can either get around this problem by

• using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances

• otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool

• or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.

Restoring the database.bak from local machine to the server

The RESTORE only works on the actual server machine - is this your own PC, or is it a separate machine??

If it's a separate machine: you cannot restore a database onto a remote server from your local harddisk - you need to put the *.bak file onto a drive that the server can reach - e.g. the server's own local drives, or a network drive that the server has a mapping (and access) to.

---

Related Topics