Friday, March 9, 2012

I need to rebuild Log file from Master file

Dear sir,
Now i have big problem with SQL i lost Log file now i have only master
file . i used recovery to find Log file but not found.
Please tell me how to rebuild Log file from Master file?
Waiting your suport,
Bunthach Eng student of Cambodia (My mail bunthachx@.yahoo.com, My phone
85512 500 382
*** Sent via Developersdex http://www.examnotes.net ***Bunthach Eng wrote:

> Dear sir,
> Now i have big problem with SQL i lost Log file now i have only master
> file . i used recovery to find Log file but not found.
> Please tell me how to rebuild Log file from Master file?
> Waiting your suport,
With master file you mean data file I presume?
Try he following (procedure by Raino Botha):
shutdown sql
move the current database file or rename it
restart sql server
create a new database of the same name and log file and location as the
old database and log file
get rid of the old database. you may be able to right click delete it
in this situation or used sp_removedb
create a new database of the right size and shape with correct log and
data file locations
stop sql
rename the new databases.mdf or delete it if you don't have enough
space - do not touch the .ldf
move back in the old database .mdf file or rename it back again
restart sql server
it should come up suspect
--
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX)
into Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL7\DATA) and rename the log file
associated the DB in question (XXXX.ldf) to some temporary name, such
as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency
mode by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log ('<DB_Name>','<log_filename>') <-- You will need the
quotation marks REBUILD_LOG should take less than 5 minutes even on a
very large
database. It should complete with the message DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window)
and restart SQL server. Verify the status of the database by running
DBCC NEWALLOC and DBCC CHECKDB on the database."
HTH,
Stijn Verrept.|||Stijn
There is no sp_removedb stored procedure. I think you meant DROP DATABASE
...command
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:JMadnf956pkAWzXeRVnyrQ@.scarlet.biz...
> Bunthach Eng wrote:
>
> With master file you mean data file I presume?
> Try he following (procedure by Raino Botha):
> shutdown sql
> move the current database file or rename it
> restart sql server
> create a new database of the same name and log file and location as the
> old database and log file
> get rid of the old database. you may be able to right click delete it
> in this situation or used sp_removedb
> create a new database of the right size and shape with correct log and
> data file locations
> stop sql
> rename the new databases.mdf or delete it if you don't have enough
> space - do not touch the .ldf
> move back in the old database .mdf file or rename it back again
> restart sql server
> it should come up suspect
> --
> 1. From a query window, set the status so that you can update the system
> tables by running the following query:
> use Master
> go
> sp_configure "allow", 1
> go
> reconfigure with override
> go
> 2. Then set the status of the DB that is giving you the problem (XXXXX)
> into Emergency Mode by running the following query:
> update sysdatabases set status = 32768 where name = '<DBName>'
> go
> checkpoint
> go
> shutdown with nowait
> go
> 3. Go into the data directory (MSSQL7\DATA) and rename the log file
> associated the DB in question (XXXX.ldf) to some temporary name, such
> as XXXX.TMP.
> 4. Exit the query window.
> 5. Then start up SQL Server from a DOS command window by issuing:
> sqlservr -c -T3608 -T4022.
> 6. Bring up another query window and verify that the DB is in emergency
> mode by issuing:
> select Name, Status from Sysdatabases where name = '<DB_Name>'
> 7. Verify that the status is 32768. If it is, then issue the query:
> dbcc traceon(3604)
> dbcc rebuild_log ('<DB_Name>','<log_filename>') <-- You will need the
> quotation marks REBUILD_LOG should take less than 5 minutes even on a
> very large
> database. It should complete with the message DBCC execution completed
> 8. Take the database out of bypass recovery mode by issuing the command
> update sysdatabases set status = 0 where name = '<DBName>'
> 9. Exit the query window and then shutdown (Ctrl-C in the DOS window)
> and restart SQL server. Verify the status of the database by running
> DBCC NEWALLOC and DBCC CHECKDB on the database."
> --
> HTH,
> Stijn Verrept.|||Uri Dimant wrote:

> Stijn
> There is no sp_removedb stored procedure. I think you meant DROP
> DATABASE ...command
You are right sp_removedb doesn't exist. Well as I said this procedure
isn't mine.
HTH,
Stijn Verrept.

No comments:

Post a Comment