Copy SQL Server '05 DB to another PC

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!
Who is Participating?
arnoldConnect With a Mentor Commented:
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).

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
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

BBRRGGAuthor Commented:
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.
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.
BBRRGGAuthor Commented:
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).

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.


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.

BBRRGGAuthor Commented:

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!
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.
It might also have an add button to add the .bak file to the list.
BBRRGGAuthor Commented:
"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?
udaya kumar laligondlaConnect With a Mentor Technical LeadCommented:
use the tutorial this explains full procedure of backup and restore
JrLzConnect With a Mentor Commented:
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

BBRRGGAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.