moving database to new server
-
I am trying to move a SQL database to a new server when I used Enterprise Manager to copy the data. The tables moved so did the stored procedures but the views were moved as tables not as views. What did I do wrong?
-
Is this SQL Server 7 or 2000? If it's 7 and you copied using the wizard, there are known problems with this. A better and probably faster option is to backup your current database, copy off the *.bak file to the new location and then perform a restore on the other server. That way you'll capture everything but the logins which can be restored using procedures available here or on Microsoft's web site. Hope this helps.
My hovercraft is full of eels.
-
SQL 2000 should I still try backup and restore?
-
I almost always move a database via backup/restore. About the only thing that you have to fuss over in doing this is making sure the logins that are on the old server also exist on the new server. If you database is small and you have a fast network connection between the two servers, this will be your fastest solution.
Another possibility would be to detach the database from the old server, copy the MDF file, and then attach it to the new server. You will still have to make sure that the logins required by the database exist on the new server.
-
I concur with Robert that regardless of version, backup and restore is usually the best and fastest way to go. One thing that may save you some time is to create a new empty database on the target server and set the size large enough to accomodate the restore. That way, the database doesn't have to autogrow during the restore process. You'll need to select the check box for 'Force restore over existing database' before doing this.
Your logins can be 'orphaned' when you restore your database and you won't be able to change thier access without getting an error. Check this link in order to fix these: http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
My hovercraft is full of eels.
-
Thanks to both of you I will try this method.
Regards
-
I suggest you the "quick" way:
- put database offline
- detach database
- copy datafiles on the new server
- attach database
That's it!
-
See if this provides additional information
http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Heather,
Backup and Restore or Detach/Attach are the safest ways agree with all other opinions
Here are 2 things to watch if you are using different techniques:
- If you use DTS Import and Export Wizard , unless you know really well what you are doing the indexes and Identity fields may be lost
-If you are using Copy Database Wizard you must be connected as a DOMAIN login using integrated Windows authentication to both servers and this login have to be an administrator in SQL server and have a lot of permissions on the File System folders. They say Copy Database Wizard is using DTS, but actually it detaches the source database, copies database files to the destination server and attaches the files there. So if you use it to copy a production database, be advised that it will be detached for a couple of minutes. Maybe it was fixed in the later service packs, but it was my experience a couple of years ago.
Yelena
Regards,Yelena Varsha
-
Thanks to all who replied. So far so good I just Backup/Restored and it seemed to work fine
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply