| Author |
Topic |
|
indyandumi
Starting Member
6 Posts |
 Posted - 2007-01-31 : 20:16:37
|
| 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
38200 Posts |
Posted - 2007-01-31 : 20:22:31
|
| 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 |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-31 : 20:22:35
|
you can also use detach and attach
KH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-31 : 20:23:31
|
quote:
Originally posted by khtan
you can also use detach and attach
KH
Not for production though! Detach takes the databases offline...Tara Kizer |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-31 : 20:27:35
|
True. It will not be possible for 24 x 7 online operations.
KH |
|
|
|
indyandumi
Starting Member
6 Posts |
Posted - 2007-01-31 : 22:01:07
|
| How do you do use detach and attach? If you can give me the step by step directions , i will so very much appreciate |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-31 : 22:06:05
|
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 OnlineUsing EM on the new server, right click on the word 'database', All task - Attach Database
KH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-01 : 00:33:03
|
| Don't use detach/attach for production databases! Detach will take the database offline! Use BACKUP/RESTORE only to move production databases.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-01 : 00:59:34
|
| 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 backup2) Restore on New server3) Prevent access to Old production database4) Take a DIFF backup5) Restore on New server6) Allow access to New production databaseThe 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%20UsersKristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-01 : 10:59:21
|
| 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=sql2kGo 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 |
|
|
|
|
copy production db to another server