BACKUP LOG MyDatabase
	TO DISK = 'D:\DATA\MSSQL\BACKUP\MyDatabase.TRN'
WITH 
	DESCRIPTION = 'MyDatabase TailLog'
	, INIT
	, STATS = 10
	, NORECOVERY

quote:

---

Originally posted by nigelrivett

>> SQL Agent has not run for months and noone noticed
Who's going to be first to say it?

---

quote:

---

Originally posted by russell

By the way, when you swapped out the files, were they the exact same size? If not, the attach wouldn't work anyway.

---

quote:

---

Originally posted by nigelrivett

You could set the database to emergency mode and copy the data out.
Do you have access to all the database files?

---

quote:

---

Originally posted by Kristen

quote:

---

Originally posted by russell

By the way, when you swapped out the files, were they the exact same size? If not, the attach wouldn't work anyway.

---

No, I'm sure they weren't - TLog will have been growing for 6 months since the last backup - if nothing else.

So ...

restore the very old backup
extend it to match the actual size of rescued MDF and LDF files
Put the database offline
Replace the files with the rescued ones
Try bringing it online

Will that do?

---

quote:

---

Originally posted by russell
Or just create the database with the files at the prescribed sizes.

---

LOG ON
( NAME = 'MyDatabase_Log',
   FILENAME = 'D:\DATA\MSSQL\LOG\MyDatabase_Log.LDF',
   SIZE = 261752KB,
   FILEGROWTH = 1MB )

Create a database with an mdf of the same size as the old one
stop the server.
Copy the corrupt mdf over the newly created one.

BACKUP LOG MyDatabase
	TO DISK = 'D:\DATA\MSSQL\BACKUP\MyDatabase_TailLog.TRN'
WITH 
	DESCRIPTION = 'MyDatabase TailLog'
	, INIT
	, STATS = 10
	, NO_TRUNCATE

DBCC rebuild_log('MyDatabase', 'D:\DATA\MSSQL\LOG\MyDatabase_Log.LDF')
GO

DBCC CHECKDB ('MyDatabase') WITH ALL_ERRORMSGS, NO_INFOMSGS
go

SELECT	[TableName] = object_name(id),
	[IndexName] = name
FROM	sysindexes 
WHERE	    id = 341576255
	AND indid = 3

quote:

---

Here are the steps to create a database from only an .Mdf:

1. Create a new database exactly the size of the old one. Log file size doesn’t matter, but the data file should be the same size precisely (see listing 1).
2. Swap your data file for the one in the newly created DB.
3. Update the status in sysdatabases.
4. Rebuild the log (see listing 2. Note the listing shows SQL 2000 syntax. See link at the end of this article for SQL 2005).
5. Perform consistency checks.
6. Set database options as desired.

If we are lucky, only user transactions were in the now unrecoverable log file and we have a few (or few hundred) bad records. Please see the article referenced above for the pitfalls of performing these actions. Again I’ll stress that this is only a last resort to recover data. Keep those backups ready! We are only as good as our last backup!

Listing 1:


USE master;
GO

CREATE DATABASE MyNewDatabase
ON
( NAME = MyNewDatabase_data,
FILENAME = 'D:\Data\MyNewDatabase_data.mdf', -- use your physical path
SIZE = 14976768KB, -- make exact size as .mdf u will swap in
FILEGROWTH = 5 )
LOG ON
( NAME = 'MyNewDatabase_log',
FILENAME = 'E:\logs\MyNewDatabase_log.LDF',
SIZE = 1024MB,
FILEGROWTH = 5MB )
GO

/*
NOW STOP SQL Server service
Delete MyNewDatabase_data.mdf at the OS level
Rename old .mdf to MyNewDatabase_data.mdf
START SQL Server service
MyNewDatabase will not be accessible yet
*/

Listing 2:

Use Master;
GO

-- allow updates to system tables
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

-- remove suspect flag, set single user
UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'MyDatabase';
GO

-- disable updates to system tables
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

/* RESTART SQL SERVER SERVICE */

-- rebuild the log file
DBCC REBUILD_LOG('MyDatabase','E:\MyDatabse_log.ldf')
-- change to your physical path
GO
-- Check db consistency
DBCC CHECKDB ('MyDatabase');
GO

-- Set DB Options
ALTER DATABASE MyDatabase SET MULTI_USER
GO

---

UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'MyDatabase';