Solved

URGENT How can i Copy a database from one pc to other

Posted on 2001-07-10
11
681 Views
Last Modified: 2008-03-10
can u help me how can i copy my database from one pc to other from floppy can it be possible.

i have tried backup,import,export but failed

please tell me in detail to take a databse and restore to another server in a floppy
0
Comment
Question by:pawankk
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 3

Expert Comment

by:mathavra
ID: 6271625
If the database is small enough, follow the stes below:

1) dump database to a file
2) If needed, pkzip or winzip it (hopefully it will be small enough to fit it in a floppy)
3) Copy the file to floppy
4) Copy the file back to the other system which is running the server (If necessary to appropriate directory)
5) run the load database from file command.

This should work.
0
 
LVL 8

Expert Comment

by:chigrik
ID: 6271720
You can use sp_attach_db system stored procedure in this case.
Read about this stored procedures in the SQL Server Books Online.

This is the example (to copy Test database):

1. Copy Test.mdf and Test.ldf files into C:\MSSQL7\data directory
   (path by default).
2. Run this from the Query Analyzer:

    sp_attach_db @dbname = 'Test',
                 @filename1 = 'C:\MSSQL7\data\Test.mdf',
                 @filename2 = 'C:\MSSQL7\data\Test.ldf'

Read also about "INF: Moving SQL Server 7.0 Databases to a New Location"
http://support.microsoft.com/support/kb/articles/q224/0/71.asp

Hope this helps
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6272965
0
 

Expert Comment

by:Dangerman
ID: 6280235
Well, this really depends on the current size of your database. I assume the two machines are not connected in any way, either by LAN or local connection? If they're not, and cannot be connected in any way, you're only option is to dump the database and restore it on the other machine, but of course the dump may exceed 1.44MB. You can get file splitter utlities, usually available as Freeware from download.com or other sites, to split large files over several disks. Alternatively you can use WinZip to zip the file up over several disks.

There are many options but a database backup/restore is inevitable if the machines are not connected.
0
 

Expert Comment

by:Dangerman
ID: 6280367
And the details depend on what version of MS SQL Server you are running. In 6.5;

On the original machine;
- create a new dump device in SQL Server
- perform a full database backup to that dump device
At OS level;
- zip the dump file across several disks on old machine
- unzip onto new machine

On the new machine;
- create database devices for the database in SQL Server (ensure same sizes are used)
- restore dump file into new database

That's it!
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:pawankk
ID: 6281629
i m using sql sever 7.0 .

i have tried backup and restore it gives an error that it cannot be restored in sigle user mode......


i have also tried
sp_attach_db @dbname = 'Test',
                @filename1 = 'C:\MSSQL7\data\Test.mdf',
                @filename2 = 'C:\MSSQL7\data\Test.ldf'


but it gives an error that it cannot restore an external database
0
 

Accepted Solution

by:
Dangerman earned 50 total points
ID: 6285059
When you use the SQLServer Backup routine, is the database in Single User mode? What's the exact error message you get when you try to restore the database?

I haven't used SQLServer 7.0 myself, but assume the functionality is similar to that in 6.5. I never have my databases in Single User mode when taking backups, I just ensure there is no activity in the database at that time.

If you've created new database devices on the new machine, make sure they are the same size as the originals. Also make sure the new database you create, prior to restoring your backup, is not in Single User mode. In 6.5 you can do this in the Enterprise Manager, by right-clicking on the DB name, selecting Edit, then look at the checkboxes on the Options screen.
0
 

Author Comment

by:pawankk
ID: 6295678
it gives me following problem please tell me is there any solution for these problems while copying or importing etc.

{
Server: Msg 1816, Level 16, State 1, Line 1
Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server.
Server: Msg 1816, Level 16, State 1, Line 1
Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server.
Warning: Unicode comparison flags 196611 in database differs from server Unicode comparison flags of 196609.
Warning: Unicode comparison flags 196611 in database differs from server Unicode comparison flags of 196609.
}
0
 

Expert Comment

by:Dangerman
ID: 6307787
You will get these errors when the SQL Server into which you are importing the backup has been installed with a different character set and sort order. In 6.5 these options are specified when you install SQL Server on the server, not sure about SQL 7 though. You need to identify which character set and sort order are set up on the original server and ensure SQL Server is configures with these on the new server.
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6875224
pawankk,

These questions are still open, and our records show you have logged in recently. Please resolve them appropriately as soon as possible. Continued disregard of your open questions will result in the force/acceptance of a comment as an answer; other actions affecting your account may also be taken. I will revisit these questions in approximately seven (7) days.

http://experts-exchange.com/questions/Q.20122806.html
http://experts-exchange.com/questions/Q.20123712.html
http://experts-exchange.com/questions/Q.20139525.html
http://experts-exchange.com/questions/Q.20145005.html
http://experts-exchange.com/questions/Q.20147035.html
http://experts-exchange.com/questions/Q.20147967.html
http://experts-exchange.com/questions/Q.20147974.html
http://experts-exchange.com/questions/Q.20148804.html
http://experts-exchange.com/questions/Q.20151819.html
http://experts-exchange.com/questions/Q.20153107.html
http://experts-exchange.com/questions/Q.20156476.html
http://experts-exchange.com/questions/Q.20156479.html
http://experts-exchange.com/questions/Q.20162106.html
http://experts-exchange.com/questions/Q.20163106.html
http://experts-exchange.com/questions/Q.20191021.html
http://experts-exchange.com/questions/Q.20193613.html
http://experts-exchange.com/questions/Q.20232146.html
http://experts-exchange.com/questions/Q.20241878.html
http://experts-exchange.com/questions/Q.20251368.html
http://experts-exchange.com/questions/Q.20261904.html

EXPERTS: Please leave your thoughts on this question here.

Thanks,

Netminder
Community Support Moderator
Experts Exchange
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6971733
Admin notified of User neglect. Force-accepted by
Netminder
CS Moderator
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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

13 Experts available now in Live!

Get 1:1 Help Now