garethgrimshaw
asked on
Attach database without log file in SQL 2005
Hi there,
We are running MOM and having problems with the database. The log file reached 86GB and filled up the whole of the D Drive it was living on.
I wanted to take a backup of the database but it kept failing, i eventually found out that this was because the external HD i was trying to backup too was FAT 32 and kept crashing at about 4GB (no alarm bells went off in my head....)
However I was able to detach and attach the database until i tried something.
I added an extra log file to the database and tried the shrink log file within Management Studio to empty the original log file and hope it would fill up the log new log file. However this didn't do anything and i detached the database again and tried copying the files onto the external drive. Again as it was fat 32 could not take the large files so formatted as NTFS and deleted the extra log file i created. I have a copy of the mdf and original ldf safe on my external disk now as well as the orignal files on the server.
When i try to attach the database it is asking for the extra log file i created. And have tried to attach the database with removing the extra log file but it still wont attach and load the database.
I was going to use sp_attach_single_file_db but it mentions to not use it on a multi logged database.
Is there anyway i can attach this database?
For the record i have no full backup of the database!
Any help is muchly appreciated!
We are running MOM and having problems with the database. The log file reached 86GB and filled up the whole of the D Drive it was living on.
I wanted to take a backup of the database but it kept failing, i eventually found out that this was because the external HD i was trying to backup too was FAT 32 and kept crashing at about 4GB (no alarm bells went off in my head....)
However I was able to detach and attach the database until i tried something.
I added an extra log file to the database and tried the shrink log file within Management Studio to empty the original log file and hope it would fill up the log new log file. However this didn't do anything and i detached the database again and tried copying the files onto the external drive. Again as it was fat 32 could not take the large files so formatted as NTFS and deleted the extra log file i created. I have a copy of the mdf and original ldf safe on my external disk now as well as the orignal files on the server.
When i try to attach the database it is asking for the extra log file i created. And have tried to attach the database with removing the extra log file but it still wont attach and load the database.
I was going to use sp_attach_single_file_db but it mentions to not use it on a multi logged database.
Is there anyway i can attach this database?
For the record i have no full backup of the database!
Any help is muchly appreciated!
ASKER
But i cannot attach it as the database now thinks there are two log files for it.
Is there a way to attach the database due to the fact i only have one of the two logs?
Thanks.
Is there a way to attach the database due to the fact i only have one of the two logs?
Thanks.
As I said if it can't find the log files it will recreate them. Trust me I've done this MANY times. If you've specified two log files then it will create two new blank log files.
ASKER
Hi there,
I have tried to re-attach the database, i use Management Studio and select the re-attach option.
Point to the mdf file and the log files are listed underneath. I remove the two log files and the there are no log files in the folders specified as i have moved them elsewhere.
When i run it i get the the following error message.
Could not open new database 'SystemCenterReporting'. CREATE DATABASE is aborted.
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
File activation failure. The physical file name "E:\REPLOG2.ldf" may be incorrect.
The log was not rebuilt because there is more than one log file. (Microsoft SQL Server, Error: 1813)
I have also tried the attach_db_single_file query and get a very similar message.
Is there another sql query i can run or am i doing something wrong with the re-attachement.
Many thanks for you help so far!
I have tried to re-attach the database, i use Management Studio and select the re-attach option.
Point to the mdf file and the log files are listed underneath. I remove the two log files and the there are no log files in the folders specified as i have moved them elsewhere.
When i run it i get the the following error message.
Could not open new database 'SystemCenterReporting'. CREATE DATABASE is aborted.
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
File activation failure. The physical file name "E:\REPLOG2.ldf" may be incorrect.
The log was not rebuilt because there is more than one log file. (Microsoft SQL Server, Error: 1813)
I have also tried the attach_db_single_file query and get a very similar message.
Is there another sql query i can run or am i doing something wrong with the re-attachement.
Many thanks for you help so far!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey there,
I have just tried that script, the database is backup online!
Thanks so much for your help, we have a couple of SQL guys who didnt know about that "attach_rebuild_log" command so you have helped them as well!
Once again, thanks.
I have just tried that script, the database is backup online!
Thanks so much for your help, we have a couple of SQL guys who didnt know about that "attach_rebuild_log" command so you have helped them as well!
Once again, thanks.
The funny thing is that in SQL 2000 you could do this with the GUI, I don't know why SQL 2005 doesn't support it. Cheers
Congrats tedbill: You just saved my Friday. Thanks!!!!!!!!!!!!!!!!!!!!
You can reattach a database without a log file and SQL will create one for you automatically. Make sure the folder you reattach from doesn't have the log file in it.