March 18, 2002 at 5:14 am
I've done it many times with no issues, no reason there should be even. The problem is that you have to stop the service on the source machine to make the copy. I've even documented the process of moving everything from one machine to another in this article:
http://www.sqlservercentral.com/columnists/awarren/20010425205439_1.asp
Andy
March 18, 2002 at 10:53 am
I want to thank everyone for the incredible help and support I received on this issue.
I restored the database to the new server using Backup Exec and used the Microsoft Procedure listed above to copy the security to the new box. It worked without a hitch.
After the restore was completed, I had to rename both servers. I did this to give the new server the old server name. After this was completed, I ran the install procedure on the new server using the SQl 7.0 CD and ran the sp_addserver and sp_dropserver stored procedures. Viola!
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
March 20, 2002 at 10:27 am
Just a quick addition regarding the TS comment.
If your server isn't running Terminal Services, you can use a remote control program to remotely start the file copy. There is a free one called VNC orginally created by ATT Labs you can find on shareware sites. There are a few derivations of it, like Tight VNC, but in my experience, the variations are a touch less stable, though fully compatible with the original.
Obviously there are some security issues to be handled if you install it, and it doesn't give you the same type of virtual desktop access to a server that Terminal Services does, but it's priced right and is easy to implement on existing servers. 🙂
Matthew Galbraith
March 21, 2002 at 10:02 am
I agree with th eissue with the detach and attach for tha database but you can scrip the logins and then detach the database copy the files to the new server attach it at the new location and run th escrip to create the logins...
March 21, 2002 at 10:28 am
Khickey:
Will that work when using standard security?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
March 25, 2002 at 7:39 am
Just curious... what's the difference(s) of using detach/attach and backup/restore of a database? I've seen people mention both and haven't yet found anything that documents the differences and/or advantange or disadvantage of one technique over the other...
March 25, 2002 at 9:51 am
I tend to prefer backup/restore, but I've been doing this since v4.2 and attach wasn't an option then. I've done both and no great difference that I can see. The backup/restore is a smaller file, usually, and copy time is less, but restore takes time. With an attach, there are a couple inserts into tables and the db is available, so it can be quicker. BUT, you have to have the original offline while you copy the detached file. Kind of two ways to do the same thing.
Steve Jones
March 28, 2002 at 9:03 am
Regarding the problem with the security profiles... When you restore the backup you have the sysusers table still, I create a script to reassociate logins based on the values in this table. Also scanning this table afterwards usually reminds me if I didn't change the dbo from a user to sa before the copy. I'm not preaching about assigning security by groups, it can dramatically simplify the login issue..
-Ken
March 28, 2002 at 9:19 am
Another comment. If you DON'T have terminal services set up, and you don't feel like installing a free third party (security hole) application on your server, simply use xp_cmdshell and do the copy from the destination SQL Server using DOS commands.
-Ken
April 12, 2002 at 9:29 am
sp_changer_users_login, for fun I'll let you look it up in BOL. Learning is half the fun and you remember it longer.
April 18, 2002 at 5:10 am
Iam not sure whether this will serve the purpose of fizzleme. But I have tried this and it works.
Generate SQL Script for the Database (we have this option in Enterprise manager)
All the users, roles can be scripted.
Run the Script on the destination.
Now take a BCP from the source and restore the BCP on the destination...
Get going...
Edited by - levi on 04/18/2002 05:12:22 AM
Jesus My Saviour
April 18, 2002 at 5:14 am
I am sorry I for got to mention running the Generated script on the Destination.
Jesus My Saviour
April 18, 2002 at 6:12 am
quote:
Can we use the detach/attach method going from SQL7 to SQL2K ? Would the difference in system tables cause a problem ?
Gladys - Yes you can and it updates the database to the new version during the attach process.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 18, 2002 at 2:17 pm
Very interesting discussion on backup restore vs detach re-attach.
Is there a technical reason for not using the copy database wizard for the whole thing?
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply