Restore .bak file to remote database
sql, sql-server, ssms
Solution
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';
Problem
I have a `test.bak` file in my local machine. I need to restore this file to remote machine's database. How do I do that? When I try this, the remote database throws an error that it is not able to find `test.bak` on the local filesystem. Query ``` RESTORE DATABASE TESTPROJECT FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.ICON3\MSSQL\Backup\test.bak' ``` Error ``` Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.ICON3\MSSQL\Backup\test.bak'. Operating system error 2(The system cannot find the file specified.). ``` How can I achieve this? I am using Microsoft SQL Server 2008.