January 7, 2005 at 3:30 pm
I want to copy a database (in an instance SQL Server 2000) from one machine to another. Normally, I would use the Copy Database Wizard, however, in this case, the two machines do not share a network. What would be the simplest way to accomplish this task?
I would like to copy all the databases at the same time, if possible; There are no user-defined databases on the SQL Server instance being copied to, so overwriting data is not an issue.
January 7, 2005 at 4:19 pm
SQL Server doesn't come with very good tools to do this....
If the database(s) is not too large you could script out the DB structure (generate SQL script, including the create DB statements...under Options) and then make a backup of the source DB. Then, burn a copy of the resulting .bak file onto some media and then move to your destination....Restore the backup to the empty DB framework generated from your script.
ciao ciao
January 8, 2005 at 12:36 pm
You don't have to do all of that scripting. If you just do a backup, copy the .bak file to CD or tape and then to the second server and do a restore, it should work. The restore is going to wipe out anything that you did when you played the script.
If the file structure is different on the second server, you may need to do the restore using the "with move" options to specify the new file location.
You can also detach the database, copy the mdf, ldf and ndf (if you have any) and attach on the second server.
If you plan to use the same user accounts, you will have to set up all of the user accounts on the second box. If they are SQL accounts -- not Windows accounts -- you will need to also then run the sp_change_users_login command. See Books On Line for more information about how to use this.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 8, 2005 at 2:49 pm
Can I simply perform a restore on a single DB? Won't I need to restore the "master" database as well? If not, wouldn't it cause a problem? ( I seem to remember doing something like this before, and data did not transfer properly b/c I only restored a single DB)
I used the scripting method for a small DB, and it worked great. I tried it again for a medium-sized DB, and the script generated many errors. The errors all seemed to be dependecy issues (i.e. attempting to create a function that references qryExample before creating qryExample). Does SQL server create scripts without considering any dependencies!? Is there any workaround for this? (i.e. FORCE option)
Please advise.
January 10, 2005 at 1:41 am
See, if this helps:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply