Category: MS SQL

Attach SQL Server database with missing ldf file

This morning I was faced with an interesting issue. Somehow, I’m not entirely sure how, all the rows from one table in a database for a bespoke application I developed were missing. After spending an hour trawling through the logs of both SQL Server and the application’s logs I couldn’t figure out why they were missing. Luckily the application isn’t mission critical!

So, onto out backup server to restore the database from last nights backup. I performed a file level restore of the .mdf file but wait a minute, where the f**k is the .ldf file? Missing from the backup 😦

To restore the database as a single file copy the mdf file to the desired location and use the following query.

USE master
GO
CREATE DATABASE [TestDatabase] ON
(FILENAME = N’C:TempFolderSP_C_Intranet-Restored.mdf’)
FOR ATTACH_REBUILD_LOG
GO

Oh dear. it looks like the transaction large wasn’t committed before the backup was taken.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘TestDatabase’. CREATE DATABASE is aborted.

There is a way around this issue but it may result in data loss so it is not recommended for a live environment. We can force SQL Server to rebuild the log using the following Query.

USE master
GO
CREATE DATABASE [TestDatabase] ON
(FILENAME = N’C:TempFolderSP_C_Intranet-Restored.mdf’)
FOR ATTACH_FORCE_REBUILD_LOG
GO

And that’s it. Now go and make sure your backups are running properly instead of pissing about.

Recovering a “Suspect” Database in SQL Server 2008

Today I broke my SharePoint development environment by shutting down the SQL Server forcibly and corrupting my transaction logs, making the databases show in Management Studio show as “Suspect”. Thankfully this was a development environment and not our live environment! After a lot of googling and perseverance, I managed to cobble together a solution to get my databases back online and continue to work.

The best way to recover from this issue is to restore from a Backup. Stupidly I didn’t have a good backup available for the databases in my Dev environment.

I DO NOT recommend this method on a live system!!!

To recover the database, which involves rebuilding the transaction log I executed the following query in Management Studio. Obviously change the database name to reflect your own database name.

USE master
GO
ALTER DATABASE [DB_Name] SET EMERGENCY
GO
ALTER DATABASE [DB_Name] SET SINGLE_USER;
GO
DBCC CHECKDB ([DB_Name], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE [DB_Name] SET ONLINE;
GO
ALTER DATABASE [DB_Name] SET MULTI_USER;
GO

Again I stress, DO NOT sue this method on a live environment.

Hacking your Microsoft SQL Server

Today a friend was complaining about how he had forgotten the sa password for his SQL Server…. Oh Dear.

If you follow Microsoft’s best practices for SQL security, as my friend did, you will have also disabled the BUILT-INAdministrators login… Oh Dear Oh Dear.

Fear not, we will have you back in within minutes, just follow these instructions:

  1. Logon to Windows on the SQL server a system administrator.
  2. Open CMD and type the following command to stop the SQL service NET STOP MSSQL, Replacing MSSQL with your server instance name, for example: MSSQL$Instance.
  3. Type NET START MSSQL /m to start SQL up in Single User Mode.
  4. Open SQL Management Studio, and connect to your server using windows authentication.
  5. Open a New Query window and type the following into the query window:

    CREATE LOGIN <username>
    WITH PASSWORD='<password>';
    GO
    SP_ADDSRVROLEMEMBER '<username>', 'sysadmin'
    GO

    Example:

    CREATE LOGIN m0rph3us
    WITH PASSWORD='12345678';
    GO
    SP_ADDSRVROLEMEMBER 'm0rph3us', 'sysadmin'
    GO

  6. Click Execute. This will create the required username, with the desired password, and add that username to the sysadmin role, granting full control of the server.
  7. Close SQL Management Studio.
  8. Open CMD and type NET STOP MSSQL .
  9. Type NET START MSSQL .
  10. Open SQL Management Studio, and connect to the server using SQL Authentication, and input the username and password we created earlier.

You should now have regained full control of your SQL server, so go ahead and change the sa password.

I know these steps definately work in Microsoft SQL Server 2005, but I have not tried it on any other version of SQL. Please let me know in the comments if you try this method in another version of SQL, and the outcome and I will updater this post.