Solved

Copy SQL Server '05 DB to another PC

Posted on 2010-11-29
15
296 Views
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!
0
Comment
Question by:BBRRGG
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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:
http://support.microsoft.com/kb/246133


It all depends on what you need it for
0
 
LVL 1

Expert Comment

by:JrLz
Comment Utility
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
0
 

Author Comment

by:BBRRGG
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.
0
 

Author Comment

by:BBRRGG
Comment Utility
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).


0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.

 

0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:BBRRGG
Comment Utility

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!
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
It might also have an add button to add the .bak file to the list.
0
 

Author Comment

by:BBRRGG
Comment Utility
"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?
0
 
LVL 12

Assisted Solution

by:udayakumarlm
udayakumarlm earned 100 total points
Comment Utility
use the tutorial this explains full procedure of backup and restore
http://www.linglom.com/2008/01/12/how-to-backup-and-restore-database-on-microsoft-sql-server-2005/
0
 
LVL 1

Assisted Solution

by:JrLz
JrLz earned 100 total points
Comment Utility
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

0
 
LVL 76

Accepted Solution

by:
arnold earned 300 total points
Comment Utility
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).





0
 

Author Closing Comment

by:BBRRGG
Comment Utility
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!

http://www.learnxpress.com/restoring-a-database-backup-in-bak-format-using-sql-server-2005.html

(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
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

10 Experts available now in Live!

Get 1:1 Help Now