03-25-06 04:55 PM
Try this undocumented stuff provided by Kevin Sun[MS].
---
==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============
--
-oj
"Jeff Turner" <zigjst@gmail.com> wrote in message
news:1143225807.978705.216100@v46g2000cwv.googlegroups.com...
> big problems, hoping someone can help. I'm trying to attach a database
> after a server failure, and it won't let me attach it with the
> transaction log I have OR w/o the transaction log and trying to get it
> rebuilt.
>
> I have 1 MDF and 3 NDFs. I created a new database with identical MDF
> and NDF filenames, shut down SQL, copied my "good" database files over
> top of them, and started SQL back up.
>
> In Management Studio the database is there, but it won't let me do
> anything -- it just tells me the database can't be opened "due to
> inaccessible files or insufficient memory or disk space".
>
> If I look in the SQL logs I find this: "The log cannot be rebuilt
> because the database was not cleanly shut down."
>
> does any have any pointers on how I can resolve this and get this thing
> back online ?
>
> the obvious answer of "restore from backup" aside... obviously if that
> was an option I'd be there right now.
>
>
> thanks!
>
[ Post a follow-up to this message ]
| 
