| Author |
Topic |
|
obinna
Starting Member
26 Posts |
 Posted - 2007-12-17 : 12:52:35
|
| I currently have a test sql server database and I am trying to refresh it with the production sql server data - to get the production current data.If I copy the production database.mdf file and the database.ldf file from the from the production server and replace it with the test database database.mdf file and database.ldf files and then restart the database, would this give me the production current data, please advise. - If not could you please advise on how I could get the productions current database.Many thanks for your help. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-12-17 : 12:56:09
|
| You take a BACKUP of the prod db and RESTORE your test db.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
obinna
Starting Member
26 Posts |
Posted - 2007-12-17 : 13:03:05
|
| DinakarTHANKS 4 YOUR QUICK RESPONSE, Can I take a backup while the database is online or do i need to stop the database and then tack the backup. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-12-17 : 13:04:29
|
| You can take a backup while the database is online.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
obinna
Starting Member
26 Posts |
Posted - 2007-12-17 : 13:08:20
|
| DinakarSo I go the the database and then right click task - > and then backup. Should the file ext be .bak because at the moment it is .EA, Also would the mdf file and ldf files be replaced.Thanks again |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-17 : 13:12:21
|
| Backup will not replace any db files, and you can use any extension for backup file. |
|
|
|
obinna
Starting Member
26 Posts |
Posted - 2007-12-17 : 13:13:45
|
| So when I restore it on my test server, wont the previous mdf and ldf file be replaced.Thangs again |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-17 : 16:44:21
|
| Will replace contents of the db, not db files. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-12-18 : 08:28:11
|
| of course, any table structure changes, SP changes, or any changes like that on test will be wiped out when you restore the production DB over top of it. Perhaps look into DTS to move just the DATA?[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
sqlsquirrel
Starting Member
21 Posts |
Posted - 2007-12-18 : 14:09:49
|
| Just to follow up from the other posts... take either a manual FULL database backup or scheduled FULL database backup and back it up either local to the production server (which I do not recommend) or to a backup or file server. Once that is done, go to your destination server and restore the database either through Enterprise Manager or through T-SQL. This restore WILL overwrite what you have on your destination database with all the current data (from when the backup was taken) and all current schema.The other thing to think about now is orphaned users. Hopefully you have moved over your SQL Server logins from the production server to the destiation server with their SIDs intact. This will keep the database users in sync with the SQL Server logins. I have an automated nightly process that handles all of this...I hope this helps! Good Luck!Brett DavisSenior SQL Server DBAFor more helpful tips checkout my blog at: http://www.lockergnome.com/sqlsquirrel/ |
|
|
|
|
Refresh my Test sql server database