How can i copy a database to another server(computer)?

[jonne]

How can i copy a database to another server(computer)?<br>I have tried both sql scripts and creating a backup but it dosent work.<br>please help me!<br>thanks <br>jonne

 

[HoustonGuy]

Try using the DTS (Data Transformation Services) Wizards for Importing and Exporting.<br><br>In the Enterprise Manager select Tools¦Data Transformation Services.<br><br>Hope this helps. <p>Cody ford<br><a href=mailto:codyford@yahoo.com>codyford@yahoo.com</a><br><a href= > </a><br>VB, VBS, Seagate Info/Crystal Reports<br>

 

[toejam]

It seems a bit sketchy, but we have had consistent success with the following technique (which is much faster than using DTS):<br><br>-on your new server, create a database with the same name and options as the database on the server you want to get the info from (you can leave the default sizes unchanged).<br>for this to work, the database and logfile have to be created on the same drive letter/path as on the machine you want to transfer from.&nbsp;&nbsp;for example, if the database and logfile for your existing database are on drive/path e:/data, then you must create the database/logfile in the same drive/path on the machine you want to transfer to.<br><br>- once you have done this, stop sql server on your destination machine, and overwrite your newly created database/logfile with the database and log files from your existing server.<br><br>-restart sql on the destination server.<br><br>-this will transfer all of your tables and stored procedures, but not any DTS packages you have created.<br><br>-make sure you create the appropriate accounts on your new server so that your set permissions work for your tables; you may have to reset permissions in some cases.&nbsp;&nbsp;You can also use DTS to import permissions from your previous server.<br><br>-done.<br><br>

 

[HoustonGuy]

Server to server is easier - even in DTS.<br><br>My delimma was transferring a test db I created on a desktop version of SQL Server on my laptop, along with views and procs onto the companies SQL Server box.<br><br>It isn't a smooth transition at all.<br><br>There has to be an easier way... <p>Cody ford<br><a href=mailto:codyford@yahoo.com>codyford@yahoo.com</a><br><a href= > </a><br>VB, VBS, Seagate Info/Crystal Reports<br>

 

[Guest_imported]

One really good way to do this is to back the original database, transfer this dump file onto the destination<br>server and then create a database of the same size of the original and restore. in most cases this is the best. though<br>you may have to look up the login id and permissions.

 

[phoward]

I backup the database through sql and copy it to laptop &gt;mssql&gt;backup&gt;folder and do a restore from device not from file, works great.

 

[foxdev]

I think what you want are:<br><br>sp_detach_db and<br>sp_attach_db<br><br>They are designed to do just this. <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[nikhilmehrotra]

Hi,<br><br>&nbsp;If you are using SQL Server 6.5, then there is an option for database transfer. Just select your source and the destination databse and server name.<br><br>After selecting just start the transfer, rest will be managed bu sql server.<br><br>Nikhil

 

[dalec]

Just creating a empty database and copying the file worked for me.

Thanks,
Dale [sig][/sig]

 

[Guest_imported]

detaching and attaching works great always.

 

[TomSark]

Bob S.:

I wouldn't use sp_detach_db for a copy action since it will detach the database from the original server.

I usually create a backup then restore on the target server. If you do this, make sure you drop and re-create your user accounts on the target database to eliminate orphaned database users...

You could also do as suggested above by creating the db, then transferring all of the db objects to the new server (can be pretty slow though).

Tom [sig][/sig]

 

[kshakes]

I have tried using sp_detach_db and sp_attach_db, but it throws up the following error(s) :


&quot;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: sort order 30 in database differs from server sort order of 52.
Warning: Unicode language locale 33280 in database differs from server Unicode language locale of 1033.
Warning: Unicode comparison flags 0 in database differs from server Unicode comparison flags of 196609.
Warning: sort order 30 in database differs from server sort order of 52.
Warning: Unicode language locale 33280 in database differs from server Unicode language locale of 1033.
Warning: Unicode comparison flags 0 in database differs from server Unicode comparison flags of 196609.
&quot;

Is it something to do with the installation of SQL Server. All I know is that, when you do a fresh installation of SQL server, there is a reference to Unicode. I am transferring the Database from a Laptop to a Server, the size is 5GB approx.

Is there a way out of this.