Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-11-01 : 14:38:03
|
| We're getting ready to completely wipe our SQL Server (7.0) and reinstall on same machine from scratch. I'm taking the advice of those in this forum who recommended detach/attach as the easiest, safest way to protect my key db during the transition. I've done some tests and like the way it works.My question concerns the state of a database when I run a copy operation versus a detach operation (I was curious about how COPY behaves). During testing, if I COPIED a database with RECOVERY option, I notice that I do not get the latest version of the database and I assume I would have to run NORECOVERY and apply the latest transaction logs to get the latest version of the DB. Yes/No?Regarding DETACH, I'm not in a position to be able to run a test DETACH on the production db and I want to be sure that when I DO detach it, it will automatically include all the latest data. What exactly happens when a database is detached? Does SQL Server automatically write the latest transactions to the db during the detach operation? When I've run a test DETACH on a test db, I notice the date on the MDF and LDF files changes to the moment I ran detach, so I assume all is cool. The dates on the MDF and LDF files for my PRODUCTION database are mid-October 2002, so I presume these dates will also change to the date/time at the moment I detach, which, again, suggests that all transactions are brought up-to-date at the moment of DETACH-ment.As an aside, can someone explain why the dates on my PRODUCTION database's MDF and LDF files have not changed since mid-October 2002? I assume, again, this has something to do with the device backup I'm running against this db, which I THINK I inaugurated around mid-October. This device backup runs a full backup (to disk) every day at 11pm and transactions backup every three hours. The size of the .BAK file created by my device backup is only 273,500 KB, while the actual size of the MDF for this db is over 2gb. What gives?Thanks. |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-11-01 : 17:51:49
|
| When you detach a database properly using sp_detach_db, it will contain all the data, nothing less, nothing more. No worries.The dates on the mdf and ldf files are updated only when the database files are closed. The database files are closed when the SQL Server service is stopped or the database is detached. On the other hand, if your database's 'auto close' property is turned on, then you'll see the dates on the mdf and ldf being updated. However, 'auto close' should be off on a production database.--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
ksw
Starting Member
24 Posts |
Posted - 2002-11-04 : 12:50:17
|
quote:
The size of the .BAK file created by my device backup is only 273,500 KB, while the actual size of the MDF for this db is over 2gb. What gives?
My guess would be that the difference between your .BAK file and your .MDF is due to the space being allocated to the .MDF, but not used. You can verify (or eliminate, whichever the case may be) this possibility by looking at the database in Enterprise Mananger. Click on the database on the Tree view on the left. Right-Click select View then Taskpad. At the top of the right pane, you should have 3 tabs, General, Tables & Indexes, and Space Allocated. (If these options don't show up, then you don't have sysadmin privledges.) Click on Space Allocated. You'll get a graph for each file the database uses. The used space (in blue) will probably be your 273,500 KB with the rest being Free (in pink) to add up to the 2GB total. You can release the free space back to the operating system by shrinking the database. Look up "shrinking databases" in the index of Books Online. It will bring up several topics to help you.--KSW |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-11-05 : 10:41:11
|
| ksw,Thanks for the tip. I had to go to SHRINKFILE since the original data file was set at 2gb in size! SHRINKFILE worked fine for my data file, but it seems not to be responding for my LOG file. When I run SHRINKFILE on my log file, query analyzer reports success, but there is no change in space allocation for the LOG. Ideas?thx |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-11-05 : 10:49:06
|
| got my answer..."Shrinking a transaction log file does not shrink the file immediately but instead causes the file to be marked for later shrinking." |
|
|
|
|
|
|
|
|
DETACH delivers most current version of db?