[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
LVL 80

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.
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

LVL 80

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 80

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 80

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 80

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 80

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 80

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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