Copy SQL Server '05 DB to another PC

Posted on 2010-11-29
Medium Priority
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 81

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.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

LVL 81

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 81

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 81

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.


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 81

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 81

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

by:udaya kumar laligondla
udaya kumar laligondla earned 400 total points
ID: 34246130
use the tutorial this explains full procedure of backup and restore

Assisted Solution

JrLz earned 400 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 81

Accepted Solution

arnold earned 1200 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 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