A procedure too re-attach a data( MDF ) file when the log file is missing or not recoverable.
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
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:
sp_configure "allow", 1
reconfigure with override
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>'
shutdown with nowait
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
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
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.
There are a lot of scripts out there that will allow you to use the msdb to auto-generate restore scripts, but what if you cannot access the msdb database. What is the easiest way to make sure I have the proper restore scripts on hand without relying on the msdb database?
One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.
ckup compression is a new feature in SQL Server 2008 that can help provide smaller sized backups and reduce backup time. This document provides guidance related to tuning options for backup performance. All of the information and test results presented here were done specifically by using the backup compression feature of SQL Server 2008; however, they apply broadly to any backup scenario whether backup compression is used or not