Copying database to another server 3

[cfFran]

Can anyone provide conceptual guidance on copying my database to another server. The BOL list a number of stored procedures that appear to be designed for this problem. I have never done the copy before and would like to hear the story from someone who has been there before.

The best I can determine the process runs something like this:
1) use sp_dboption 'MyDatabase', 'offline', 'TRUE' to put the database offline
2) use sp_detach_db = 'MyDatabase' so the files can be copied and moved
3) use sp_create_removable to make it removable
3) use sp_certify_removable to check it before moving forward
4) copy c:\mssql7\data\myData.mdf to a removable media
5) use sp_attach_single_file_db 'MyDatabase','e:\myData.mdf' to attach the database
to my other server.

Any advice will be appreciated.

 

[JamesLean]

You might find it easier to use the Copy Database wizard from within Enterprise Mgr. Just right-click on your server and go to All Tasks -> Copy Database Wizard...

 

[MaffewW]

I think if you are copying the database (ie not moving it) you shouldnt use sp_dettach as then you'll need to reattach it to the original server. You should be able to get away with stopping the SQL Server service in control panel, then copy the .mdf and .ldf to the desired locations on the new server, then use the sp_attach_db to reattach them. Thats how I've done it and it was dead easy

eg

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

Alternatively you could use backup and restore

 

[tlbroadbent]

It is much easier than you've outlined. Take a look at the following. Be sure to read the articles listed at the end of the first article.

INF: Moving SQL Server Databases to a New Location with Detach/Attach

http://support.microsoft.com/default.aspx?scid=KB;en-us;224071

INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE

http://support.microsoft.com/default.aspx?scid=KB;en-us;304692&

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[administratorx]

I would suggest that you do the backup and restore. Also make sure if you use the detach and reattach that you copy to compatible servers !!! Some people out there don't think about that. There is also a bunch of tricks that you can do with the mdf and ldf files but there is also a bunch of problems with it too if you don't think it through.

 

[synapsevampire]

I use detach and attach frequently because it's MUCH faster than all of the other methods (if you're bringing over lots of data).

Backup and Restore is a decent 2nd choice, scripting with BCC being 3rd, and DTS coming in a solid last for performance (unless you write a custom DTS package).

The only question is whether you need it to be removeable, I don't need the system catalog tables, so I don't do that step.

You can use Enterprise Manager to do this too, just right click the database and select All Tasks->Detach Database.

I suggest that you shrink the database and exec sp_updatestats too prior to detaching it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[cfFran]

I would like to thank all of you for your excellent responses. The answers provided are exactly the kind of real-world information that I was after:

* The response by tlbroadbent pointing out the two articles on the MS Knowledge base were particularly useful.

* synapsevampire provided comparative performance data that is essential input for the decision process.

* JamesLean had a great suggestion for ease of use. However, I don't think that the wizard exists in MS SQL Server 7. At least, I couldn't find it.

Thanks Tek-Tippers.

 

[tlbroadbent]

Using Detach/Attach is faster than Backup/Restore but often not feasible because production databases cannot be shutdown or taken offline. When we need to copy an entire production database to the development server, we usually Restore the production database backup on the development server. Doing this does impact the user community. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[synapsevampire]

Good point, Terry. Although if you're using SQL Server as the database, nobody can realistic expect it to NOT go offline on occasion...

Since I can copy a 10 gig file in about 5 minutes, the extended performance degradation experienced for a backup/restore is much more significant to our community than a <10 minute disconnect, it really is a question of the least impact of your users requirements.

You can also shrink the DB and update stats AFTER copying, to minimize down time.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SQLSister]

Actually the timing of when to do the job can also be important. If at all possible, do it when the users are not online. If you have a 24-hour database, such as the backend to a website, then do these types of tasks during the time period of least use. If you must take a production database down for a nonemergency reason when the users are present, at least let them know first and make sure it's a good time. If they are doing payroll or end of the month processing, they might kill you for even a five minute shutdown!

 

[marchanje]

I hope it's ok, to add a new but related question to this thread!

I am getting ready to move to a new SQL Server 7.0

Below is my simplified task list:

Move Objects    Method          Status
=============	===========     ===================
User logins     script          Done 
User databases  detach/attach 
MSDB            unknown
DTS packages    unknown

On the new server we have already created a new database that does not exist on the current server.

After I move the user databases, then move MSDB and the DTS packages, is there a potential problem, with my scheduled jobs or something else as the dbid’s will no longer be the same? There are no jobs on the new server.

Thanks, Marc

 

[petermeachem]

Is there a problem with just stopping SQL and copying the data and log files over? I need to copy from real SQL to MSDE the easiest possible way. This seems to work.

 

[tlbroadbent]

Marc,

The articles I referenced earlier in the thread tell you all about moving system databases such as MSDB. DTS packages are stored in MSDB unless you chose to use structured files for storage. Jobs are stored on MSDB, also. Make sure you read the instructions regarding orphaned database users.

Peter,

Stopping SQL and moving files is essentially the same as detaching and moving. If you can take a server down for the duration of the move, I don't see any problems with your procedure. Timing, as SQLSister mentioned, is important. Do you move the system databases, also? If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm