MS SQL Server - Pretty big Trouble

[dOBsON]

Is this the right place?

I am trying to understand a particular disasterous action that one of my colleauges took within MS SQL server yesterday. We are working on a pretty big Business Intelligence project -Cyrstal Reporting\Business Objects.

Apparently MS SQL Server creates a transaction log based on the database, and this log can grow exponentially. Our database size is round 14gb and this log had grown to almost 123gb leaving us panicing for disk space. This file "bi_log.ldf" was copied to a different box with capacity to handle the size, and subsequently deleted from the business intellegence box.

Upon re-entering sql server to open the database again and start a new log, we found that the database was "gone" \ "corrupted" \"Suspect". Holy Crap eh- theres a full years work in that db . So we copied the log back into the same directory on the database and still wouldnt open.

Can anyone out there elaborate on this a litte\ a lot.
Again hope this is the right spot.

All words of wisdom greatly appreciated\needed
Thanks

[EzyStvy]

My educated "guess" is that when you find a talented SQL geek you'll be able to "join/attach" the file and get back up and running.

I support an accounting package that uses SQL. Have had a couple of clients basically do similar things like you've done. They tried moving both the raw mdf and ldf files to a new sql server....There is a series of commands that'll need to be run from a cmd prompt..

[DougL]

I work in schools installing new servers and part of the process is to copy over the School Information Management System (SIMS) database. after copying over the database i have to run the dbmove.exe file to reattach the sims.ldf and sims.mdf as just copying over to the same directory does not work. When you run the dbmove.exe it creates a backup then reattaches the files. Have you got any ldf,mdf backups anywhere else?

Doug

[ffreeloader]

Apparently MS SQL Server creates a transaction log based on the database, and this log can grow exponentially. Our database size is round 14gb and this log had grown to almost 123gb leaving us panicing for disk space. This file "bi_log.ldf" was copied to a different box with capacity to handle the size, and subsequently deleted from the business intellegence box. Upon re-entering sql server to open the database again and start a new log, we found that the database was "gone" \ "corrupted" \"Suspect". Holy Crap eh- theres a full years work in that db . So we copied the log back into the same directory on the database and still wouldnt open.

When you say "deleted", just what exactly do you mean? Did you delete the .ldf file manually through the OS or did you drop it through SQL Server?

[dOBsON]

Yep literaly copied it to another box and deleted the .ldf file.
But now we have copied the ldf file back and it wont "link" up with the database in sql -i gather! The mdf file was not moved\touched!!

Not moved to another sql server - was just placed on another box with sufficient space

[EzyStvy]

WARNING - Use the following at your own risk!!

Highly recomend you confirm the following with a knowledgeable SQL geek.

Below are the commands I mentioned above...


**** Replace pmData in the instructions below with the name of Your database.

1. Make sure SQL is running

2 . Go to a command prompt

3. Type osql -S (local) -U sa -P (this is case sensitive) if they changed the sa password add it after the -P



4. at the 1 prompt type

sp_attach_db @dbname = 'pmData', @filename1 =

'c:\mssql7\data\pmdata.mdf', @filename2 =

'c:\mssql7\data\pmdata_log.ldf'

change the dbname and file location as needed



5. at the 2 prompt type

go

you should get a message about succesful completion.

6. type

exit