SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 copy production db to another server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indyandumi
Starting Member

USA
6 Posts

Posted - 01/31/2007 :  20:16:37  Show Profile  Reply with Quote
I have a brand new database server with system databases.
I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate

tkizer
Almighty SQL Goddess

USA
36817 Posts

Posted - 01/31/2007 :  20:22:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes you can do that. The database does not need to exist first. RESTORE DATABASE will handle everything.

BACKUP/RESTORE is the best method to copy production databases.

Tara Kizer
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 01/31/2007 :  20:22:35  Show Profile  Reply with Quote
you can also use detach and attach


KH

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36817 Posts

Posted - 01/31/2007 :  20:23:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by khtan

you can also use detach and attach


KH





Not for production though! Detach takes the databases offline...

Tara Kizer
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 01/31/2007 :  20:27:35  Show Profile  Reply with Quote
True. It will not be possible for 24 x 7 online operations.


KH

Go to Top of Page

indyandumi
Starting Member

USA
6 Posts

Posted - 01/31/2007 :  22:01:07  Show Profile  Reply with Quote
How do you do use detach and attach? If you can give me the step by step directions , i will so very much appreciate
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 01/31/2007 :  22:06:05  Show Profile  Reply with Quote
Only if your system can some affort downtime.

Use Enterprise Manager on production Server,
- right click on the required database, All Task - Take Offline,
- then copy the MDF / LDF to the new server.
- righ click on the database again and All Task - Bring Online

Using EM on the new server, right click on the word 'database', All task - Attach Database


KH

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36817 Posts

Posted - 02/01/2007 :  00:33:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
Don't use detach/attach for production databases! Detach will take the database offline! Use BACKUP/RESTORE only to move production databases.

Tara Kizer
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/01/2007 :  00:59:34  Show Profile  Reply with Quote
Well, the database is going to have to go off line in order to MOVE it, isn't it?

You will need to stop people updating the Old database until the New database is in place.

However, you should be doing a dry-run of this - so using DETACH means taking the live database offline for the dry-run too - probably a bad idea. (If you ARE going to use DETACH make sure you have a current Backup too in case the re-attach fails - that kinda negates its use for me too - if you've got a backup why use detach?)

I reckon for a move of a production server you basically need to do:

1) Take a Full backup
2) Restore on New server
3) Prevent access to Old production database
4) Take a DIFF backup
5) Restore on New server
6) Allow access to New production database

The time between 3 and 6 will be very short, and will ensure there is no data loss.

More detail here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Move%20database,Moving%20to%20a%20new%20sql%20server,Fix%20Orphaned%20Users

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36817 Posts

Posted - 02/01/2007 :  10:59:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
If you are truly moving from one server to another rather than just copying to a test/dev environment, then you should use this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Go ahead and move all databases, including system ones so that you don't have to recreate logins, jobs, DTS packages, etc...

Just make sure that when you start that the paths on both servers are the same. This means that you selected the same drives and folders at installation time.

Tara Kizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000