did I overwrite my production .mdf and .ldf files. the date modified has been changed.

Question

did I overwrite my production .mdf and .ldf files. the date modified has been changed.

New_to_being_DBA

SSCrazy

Points: 2064
More actions
November 16, 2006 at 6:08 am

#116338

Yesterday I restored a database using a full backup and transaction logs from the production database. Essentially, I just made a copy of the production database and called it...production_database_Copy. I did it manually using EM. While restoring one of the transaction logs I noticed the .mdf and .ldf files of the production database where in the Restore As box of the Restore database option tab. It displayed d:\MSSQL\data\production_database_log.ldf and e:\MSSQL\logs\production_database.mdf file, instead of d:\MSSQL\data\production_database_Copy_log.ldf and e:\MSSQL\logs\production_database_copy.mdf. I immediately changed it to point to the .mdf file and .ldf file of my copy database. This leads me to believe that I mistakenly restored one of the transaction log files or the backup file to the production .mdf and .ldf files because I do not remember changing it earlier in the backup sequence. Although my restore as database always displayed: production_database_copy. Now the .mdf file and .ldf file of the production database has a date modified of when I was doing the restore. My question is when I restored the database (transaction_log) as copy but did not change the .mdf and .ldf files name, did I modify any of the data or log files for my production database?!?!?!? Although the backup file/transaction log is from the production database.
Also, the full backup of the production database this morning at 5am is smaller than the full backup from yesterday at 5am. 🙁 This leads me to believe something went wrong. Granted some data was missing due to me inadvertently deleting a table...the whole reason why I restored a copy of production. But I restored the table (with about half an hour or data missing), which was about 50 rows. But the difference in the backups is about 800MB. Does that sound right? When I query the data using copy and production databases, all the rows come out equal, except for 2007 data which makes sense because that has been updated/added since last night.
Please help, as I need to do a fix right away if I over-wrote the production database. 🙁

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

i think you did

i did something similar on one of our QA servers a few months back. they had a db and needed the same db restored under a different name.

Not sure how you do it in EM, i always restore via query analyzer or Veritas, but you have to use the move option to rename the physical files.

You need to restore the last back up of the production db. for future reference here is a sample restore script that I use.

RESTORE DATABASE

FROM DISK = 'pathandfilename.bak'

WITH

REPLACE,

MOVE 'logical_filename' TO 'd:\physical.MDF',

MOVE 'logical_filename' TO 'd:\physical.LDF',

MOVE 'logical_filename' TO 'd:\physical.LDF'

just edit this for the amount of files you have and their locations

if you don't want to use QA, then use EM and just restore into a different folder than production

New_to_being_DBA SSCrazy Points: 2064 More actions · Answer 2

Ah great! This totally sucks! My question is how can you over-write the .mdf and .ldf files when the restore as database displays another database name. My restore as database name says: copy but the files say production. How does that work?

Also, can you use that same scipt if your files are not on a disk but in a file. Because when I tried to restore the backup and transaction log files from QA I received an error message that said something like the transaction logs can not be used. They are part of a backup plan or something like that. My transaction log files are done thru a maintenace plan and are not on a device, like my backup. 🙁

Backup is in same file but also on a device? How does that work?

Transaction log files are in the same file as backup but not on a device. 🙁

New_to_being_DBA SSCrazy Points: 2064 More actions · Answer 3

Also, when and how do .mdf and .ldf files get modified? All my .mdf and .ldf files have the same time and date...even northwind.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

You just need to give a desired database name and change the file paths in your restoring database. It is very easy to follow in EM.

N P-286063 SSC Enthusiast Points: 164 More actions · Answer 5

I empathise with your problems. It's not exactly obvious the first time around that through EM you have to specify different paths. I tend to make different folders within the data directory - that way, the paths/filenames are definitely different. Good luck with the new job!