| Author |
 Topic  |
|
|
indyandumi
Starting Member
USA
6 Posts |
 Posted - 01/31/2007 : 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
USA
35007 Posts |
Posted - 01/31/2007 : 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)
Singapore
16746 Posts |
Posted - 01/31/2007 : 20:22:35
|
you can also use detach and attach
KH
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 01/31/2007 : 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)
Singapore
16746 Posts |
Posted - 01/31/2007 : 20:27:35
|
True. It will not be possible for 24 x 7 online operations.
KH
|
|
|
|
indyandumi
Starting Member
USA
6 Posts |
Posted - 01/31/2007 : 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)
Singapore
16746 Posts |
Posted - 01/31/2007 : 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 Online
Using EM on the new server, right click on the word 'database', All task - Attach Database
KH
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 02/01/2007 : 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
United Kingdom
22191 Posts |
Posted - 02/01/2007 : 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 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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 02/01/2007 : 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=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 |
|
|
| |
Topic |
|
copy production db to another server
