Copy SQL Server '05 DB to another PC

Posted on 2010-11-29
Last Modified: 2012-05-10
I have a 2005 SQL Server DB on Computer A (Windows XP) that I need to copy onto new Computer B (Windows 7, also SQL Server 2005.)

I thought I might be lucky enough to just copy the whole folder within SQL Server directory on Computer A and paste into AQL Server program directory on Computer B, but alas, this does not work.  I've searched some of the solutions for this task and they all seem to involve some degree of scripting and/or SQL.  I can follow these procedures if they're simple enough and spelled out in painstaking detail, but I'm more of a GUI / Wizard / Point-and-click type of person.  Can anyone provide this type of solution for this task?  Thanks!
Question by:BBRRGG
  • 7
  • 5
  • 2
  • +1
LVL 77

Expert Comment

ID: 34236224
You backup the database on server A and restore it on server b.
right click, tasks, backup database on server A
Copy the backup file to Server B
right click,tasks restore database, select the filename you copied set the options and you should be done.

You need to transfer the logings:

It all depends on what you need it for

Expert Comment

ID: 34236239
The point is to copy your database files (not the whole SQL Folder), and deploy it on computer B

1. install SQL 2005 on computer B (do not copy the folder from comp A)

2. on comp A, search for your database files, usually in .mdf and .ldf extension and copy/transfer it to computer B. If you fail on copying the files, stop the SQL Service on comp A (or detach the database)

3. on computer B, open SQL Server Management Studio. On object explorer, right click on database folder, and click attach. Locate your mdf file (and it will verify for the ldf file), and click OK to attach the database.

hope it helps, another option is to backup the database on comp A, and restore it on comp B

Author Comment

ID: 34236953
With regard to the .mdf and .ldf file, is that the SQL Server Database Primary Data File, which I find in the directory:  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

I'm seeing that this file (SQL Server Database Primary Data File) has the name of my database, and is rather large (~ 200 mb), so I assume it contains all the data, though not seeing either of the extensions you mention in the properties.
LVL 77

Expert Comment

ID: 34239931
Go through the backup/restore process as well as the transfer of the logins I posted.  You would be done within an hour from starting the process.

Author Comment

ID: 34245559
Arnold-- for the method you propose:

"Copy the backup file to Server B"
what destination folder on Computer B do I paste to?

"right click,tasks restore database, select the filename you copied set the options and you should be done."
What do I right click on?  When I open SQL Server on Computer B, all thats showing are System Databases (ie. master, model, msdb, tempdb).

LVL 77

Expert Comment

ID: 34245691
It does not matter as you will not to locate the file when you are performing the restore using the GUI.

I.e. backup the database on server a then navigate to that location open a location on serverb copy the .bak file from the location on servera to the location on serverb.

What state do you want this database to be after the restore?  Are you planing to running queries against it, or do you want to set it up as a hot standby/mirror/log shipping?

you can do it while you are on servera
Using the SSMS connect to serverb.
Right click on the database, tasks, restore database.
Then hit the browse button for the back up file (note that you will be looking at the directory structure on serverb).  Locate the file. under the options, make sure the location to which the data/log file default is the location where you want them.

On serverb, right click on the databases, tasks, restore.  the restore option does not limit your ability to restore a new database just because you right clicked on an existing one or the top of the hierarchy (Databases).
You are restoring a new database from backup, this database will only exist after the restore process completed.


LVL 77

Expert Comment

ID: 34245706
Make sure to use the login transfer.
Add the stored procedures from MS link to both and run it on both.

This way you can see whether you have the same login accounts.

Note that while the SQL username is the same, the SIDs are not.  The database security settings reference the SIDs.

Since serverb is new and inactive, you should consider syncing the logins (non sa)
i.e. if you have BBRRGG on both, you should delete BBRRGG on serverB and then use the create login directive from running the MS stored procedure on servera to create the same username/sid/password on serverb.

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 34245719

Thanks again for the feedback!

"What state do you want this database to be after the restore? "
I just plan to run queries against it

"you can do it while you are on servera using the SSMS connect to serverb."
I don't believe this is possible, as I'm not working on a network.  Just one single PC (computer A) to another single PC (computer B).  No common network though, unfortunately.

"On serverb, right click on the databases, tasks, restore..."
Do you mean right-click on the .bak file wherever I've saved it on computer B?

Thanks again!
LVL 77

Expert Comment

ID: 34245766
how are you transferring the backup file from servera to serverb, USB jumpdrive, etc.?

You have to be using SSMS on serverb. After you connect to serverB and have the listing of server/databases, right click on databases and select tasks\restore.  once here there is a reference on whether you are restoring from tape or file, select file. Within the file, there is the browse option(dot or two dots to the rights of the file field) that will let you navigate the filesystem to locate the .bak file.  Once you do you are almost done.  The name of the database will be derived from the file.
There is the options section dealing with the location where the restored MDF and LDF files will go.  Depending on your install, the default location is c:\program files\microsoft sql server\mssql\mssql.1\data and LOG respectively, if you want these files in a different location, you need to modify the location accordingly.

Make sure to select the option for the state of the database after the recovery to be online.
LVL 77

Expert Comment

ID: 34245769
It might also have an add button to add the .bak file to the list.

Author Comment

ID: 34246049
"how are you transferring the backup file from servera to serverb, USB jumpdrive, etc.?"
Yes, USB drive.  I paste the .bak file from Computer A into a folder on Computer B?

"You have to be using SSMS on serverb."
I assume this is SQL Server Mgmtm Studio Express?  Yes I'm using this.

I click on 'Databases --> Restore'
In 'To Database', I type in a name for new database.

For 'Source for Restore,' I select 'from Device' and then find the .bak file I pasted.  It appears under 'Select the backup sets to restore.'  I mark a check in the box next to this file I just added and select 'OK.'  

I then get an error that the Restore Failed : Directory Lookup for the .bak file failed with the operating system error 3(the system cannot find the path specified)

Any thoughts on what may be causing this error?
LVL 12

Assisted Solution

udayakumarlm earned 100 total points
ID: 34246130
use the tutorial this explains full procedure of backup and restore

Assisted Solution

JrLz earned 100 total points
ID: 34246641
it might be because of ntfs file permission problem.
you can try copy the bak file to your SQL Server installation folder (the folder is called backup),
and retry the restore

LVL 77

Accepted Solution

arnold earned 300 total points
ID: 34250015
Outline step by step where you place.

Lets try it this way:
open SSMS and access the SQL server on serverb.
Right click on the top of the hierarchy where the serverb\sqlexpress and get properties.
Navigate to the Database Settings.
Now look at where the DATA and LOG files will be stored.

Copy these entries into notepad.

Is this path equivalent to where the data is stored on ServerA?

I believe I provided info that when you run the restore, you must go to the options section (the second item in the left pane)  of the restore process that deals with the state of the database after the restore, as well as where those files should be restored to.

Are you copying the backup file onto serverb or are you trying to restore the file from the USB drive while it is plugged into serverb?

Permissions would not be of an issue if you are accessing SSMSE as administrator (i.e. using windows login versus sql login).


Author Closing Comment

ID: 34276636
I think this is what you all were trying to instruct me to do... anyhow, manually fixing the path for .mdf and .ldf files under Restore DB --> Options tab is what fixed it (from the following website).  Thanks again for all your help!

(6) The error can be fixed by changing the Restore As path for both data and log file. The correct path should be

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<<DATABASE_NAME>>.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<<DATABASE_NAME>>.ldf

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now