How likely is it that the log file contains uncommitted transactions? If the answer is “exceedingly unlikely,” you should be able to reattach the data file without the log file. Open a query window, make sure you’re in the master database, and run:
EXEC sp_attach_single_file_db @dbname=‘XXXX’,@physname=N’C:\xxxx1.mdf’
GO
If the mdf file is clean, SQL Server should attach the data file and create a new log file.
“We don’ need no steenkin’ backups!”, said no DBA ever.
I agree with the concept of backing up the original database, then restoring it to the new location.
Thanks for all the support so far!
Luckily I did backup the database before doing the detach/attach thing. But I don’t currently have it available.
Another question: Where do I run these commands? All I have available is an icon in the notification area that shows that the server is running, and an application called dbmanager.exe (I guess this is a frontend GUI) that I did the backup and detach/attach from.
Also another problem: Seems like the physical computer has problems too, it says that the database is flagged as SUSPECT and cannot open it.
Yay for backups!
Look in the Program Files on the server. You should have SQL Server Management Studio in there under Microsoft.
That’s where you can run the stuff we’re talking about.
The only question is how they’ve got the user access set up - you may have to get the vendor to give you login details.
You’ve definitely got some corruption.
Hopefully it is just the files rather than in the database itself.
You need to get that backup file.
Troutman, lol. OK, yeah, you can mess up a db if you’re restoring over, I was talking about making copies. You may get a bad backup, but that’s not going to muck the original.
cwthree, that’s the question, isn’t it. s/he’s got no idea of the state of anything related to the database.
This has worked for me in similar circumstances.
Ok guys, I got hold of the backup file and restored everything. Now it works both in the physical and virtual machines.
I believe the db got corrupted because at some time I pulled out the USB stick that contained it without stopping the server first. Not a good idea to keep a database on removable media.
That’s good news, congrats!
Heh, that will do it.