July 30, 2004 at 3:02 pm
Hello All,
I am a SQL server newbie. Please explain me how to copy database from one server (SQL 2000/Win2K) to another (SQL 2000/Win2k3).
I've read up on the copy database wizard and backup/restore but havent got a clear idea. When I tired using Copy Database Wizard...it gave me the 'Failed to create the OMWWIZE share'. I figured it to be a permissions issue. I dont know how to get that fixed. My source server has system account as the start up account and the destination server has my windows account (and I am an administrator on that machine).
I dont know how to do the backup/restore method. I did try something like this: created a backup of a database in the source server and copied that back up file to the destination server and used the restore option in the Enterprise Manager but it bombed becoz of the default path conflict and directed me to use the WITH MOVE option. So then I used the query analyzer to do the restore and this is what I ran unsuccessfuly:
RESTORE Database FuseTalk from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FTBKUP.bak'
With Recovery, move 'FuseTalk.mdf' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FuseTalk.mdf',
move 'FuseTalk.ldf' to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FuseTalk.ldf'
Please let me know, step-by-step (newbie remember?) how to copy databases from one server to another.
Thanks a bunch.
Ram.
July 30, 2004 at 4:59 pm
This is a nasty error whoch appear when you have permission issues - you need SQL account to be an admin account on both servers. Make a search in MS website - they have an article about this.
The easiest is to use Copy database wizard after you fix the permissions, but then you will not have jobs, users, etc. copied for you. You need to to recreate them manually.
You could also use the backup and restore wizard in EM.
There's one more very easy way if you just want to move the db - detach the DB from the old SQL server, copy the files - mdf, ldf, paste them in the new directory and atach them to the new server.
Right click on the database name, from all tasks choose detach.
Go to the SQL data folder - copy the mdf file.
Paste it into the new data folder on the new server.
Do the same for ldf file.
Right click on the sdatabase folder - from all tasks choose attach. Verify the file with the verify button. You are done.
Hope this will help.
MJ
August 2, 2004 at 2:37 am
This issue basically a migration of database from server A to server B.
Best option is take the full backup of the database on the source server, transfer it to the destination server.
To transfer the user details between the servers there are microsft supplied scripts.
The link http://support.microsoft.com/default.aspx?scid=kb;en-us;246133.
It is to migrate the database from sql server 7.0 to 2000 for the logins.
August 2, 2004 at 8:20 am
Hi, I'm sorry for the delay, i had a virus attack at home...
Your script will work just fine, if you have the right logical names. ZRun restore fileoption -(check the syntax anyway) to get the logical names of your dbs... then replace them after the move keyword. very often the names are like "_data". Other way your script is fine.
Hope this helps,
MJ
August 11, 2004 at 11:45 am
This can be done using DTS wizard (follow the instructions), however the user passwords will not be copy from server A to Server B. You need to extract sysusers table from the master table and append it to server B because the master table in server A have all the logins and password to the database that being transferred.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply