SQL Server database export to another computer

Question

I use SQL Server Managment Studio, SQL Server Express. I gererate a script with schema and data from Task -> Generate Script. And I try to import this script in other computer with the same SQL Server but this giving me error :

Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WHFM.mdf" failed with the operating system error 3 (The system cannot find the path specified.).

What should I have done to import /export entire database from one PC to another .

Did you put the file onto the actual server machine where that SQL Server instance runs?? You cannot restore from a local file to a remote server! — marc_s
– marc_s, Commented Mar 22, 2013 at 15:14
I place db.mdf, db.log files into my sql directory /DATA and then run the script — Krasimir
– Krasimir, Commented Mar 22, 2013 at 15:16
You need to first attach the .mdf to your SQL Server Express instance, before you can use it — marc_s
– marc_s, Commented Mar 22, 2013 at 15:17
how to do this .please show me how , and i will accept the answer — Krasimir
– Krasimir, Commented Mar 22, 2013 at 15:20
MSDN SQL Server Books Online is a wonderful and entirely freely available resource for these kinds of things! Learn to use it! You would have found no doubt: How to: Attach a Database File to SQL Server Express which walks you through all the steps necessary..... — marc_s
– marc_s, Commented Mar 22, 2013 at 15:22

jerrylagrou · Accepted Answer · 2013-03-22 15:53:40Z

If you read through the first few lines of the script, you will see code that specifies the name and location of the files of the database. In your case look for 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WHFM.mdf' in your script. Then look at the directory structure of the target machine. You will need to either:

1. create a directory 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\' and make sure the sql instance on the machine has RW access to that directory.

or much better,

2. Modify the script to put the file in a location that the target server wants to put files.

In regards to #2, you can have the target server give you a hint as to where to put files by right-clicking on "Databases" in Microsoft SQL Server Management Studio and create a script to create a database. The directory of the files in the create database script from the target-server should show you what to do.

Collectives™ on Stack Overflow

SQL Server database export to another computer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related