Hi All,
Using SQL2000, what is the safest and easiet way to move database data to new server for testing?
After I have it on the new server I will need to test to ensure things are working properly and after all testing is complete will need to retake the latest data so this server now becomes to the production server, will i be able to overwrite?
Also, which method have you used and how long does it take? did you run into issues?
Thanks for any suggestions here!
I usually do a backup and restore for this. You'll want a backup of the old database anyway before you turn on the new one. Depending on the size of the database, it really doesn't take all that long.
There is a restore option that will overwrite an existing database.
Wolffy
.-- ----- ..-. ..-. -.--
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
ok, hope this doesn't sound ridiculous...I shouldn't have to move master, model, msdb, right?
Nope. Go ahead and try it -- it pretty simple. Just don't overwrite the production DB my mistake. (BTDT)
Wolffy
.-- ----- ..-. ..-. -.--
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
eek...that would give me a heart attack.
do i have to create that database on the new server in order to restore the backup?
Nope. Restore will do that for you. You MAY need to change the location of the created database however. By default it will try to restore it to the same path and file name. This is under options, I think (in 2005 anyway).
Wolffy
.-- ----- ..-. ..-. -.--
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
After some thought, you could also detach the production database and copy the DB and LOG files over to the server and attach the copy to the new database. This assumes you can take the production database offline during the process.
I need to do a test, but I think the DB file is actually larger than the resulting backup however.
Wolffy
.-- ----- ..-. ..-. -.--
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
when you say take the db and log file you mean the mdf and ldf files?
also, have you ever tried the transact sql method? curious on how well this works and how/where to run it.
to backup, use on current production server:
run on new server? :Code:BACKUP DATABASE YOURDBNAME TO DISK = 'd:\backups\yourdbname\yourdbname.bak'
Code:RESTORE DATABASE YOURDBNAME_new FROM DISK = 'c:\backups\yourdbname\yourdbname.bak' WITH MOVE 'yourdbname' TO 'd:\Program Files\Microsoft SQL Server\Data\YOURDBNAME_new.mdf' MOVE 'yourdbname_log' TO 'd:\Program Files\Microsoft SQL Server\Data\YOURDBNAME_new_log.ldf'
Yup, those two.
The T-SQL above is what the Backup and Restore menu items do under the covers anyway, so I guess I have.
Wolffy
.-- ----- ..-. ..-. -.--
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
Posting Permissions 
LinkBack URL
About LinkBacks
Reply With Quote
Bookmarks