Restoring the database.bak from local machine to the server

sql sql-server-2008 database-restore
Mr A picture Mr A · May 13, 2011 · Viewed 13.1k times · Source

I am trying to restore the whole db with diagrams nd foreigns keys to the existing database on the server i want to replace that with the new one , I tried the following script with no success

drop database  DuxburyCaravans    
go
RESTORE DATABASE stonestore
  FROM DISK = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\DuxburyCaravans.BAK'
  WITH MOVE 'DuxburyCaravans' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DuxburyCaravans.mdf',
  MOVE 'DuxburyCaravans_log' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DuxburyCaravans.LDF'

but it comes up with errors:

Msg 3702, Level 16, State 3, Line 3
Cannot drop database "DuxburyCaravans" because it is currently in use.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\DuxburyCaravans.BAK'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Answer

marc_s picture marc_s · May 13, 2011

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 questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More