DigitalJargon

RSS

Recovering the Master Database from Scratch on SQL 2005 and SQL 2008

I recently ran into a situation where multiple servers lost their data  and log drives that housed the user and system databases. The servers still had valid SQL installs since the system drive was still available. Instead of re-installing SQL Server and service packs/hotfixes from scratch I used the steps below to create a new master database, restore the backed up system databases, and finally restore the backed up user databases. This method was much quicker and easier than uninstalling/re-installing SQL from scratch.

SQL 2008

  1. Copy the SQL 2008 installation media to the server
  2. Open a command prompt and go to the install directory above and run
    1. setup /ACTION=REBUILDDATABASE
      /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= /SAPWD=
      1. Use your account or an AD group for the SQLSYSADMINACCOUNTS flag. This account/group will have sysadmin rights with the new master database.
      2. Use a random complex password for the SAPWD flag. It won’t be needed after master is restored from backup.
  3. Start server in single user mode (use –m flag in the SQL server startup parameters in configuration manager) and log in with Management Studio
  4. Run script to restore database
    1. RESTORE DATABASE master FROM DISK = ‘L:\SQLBackups\Master0.bak’ WITH REPLACE;
  5. Remove single user flag and restart SQL
  6. Restore model and MSDB like a normal database
  7. Restart SQL agent
  8. Restore user dbs

SQL 2005

  1. Find a SQLSetup*.log file from the original install and open it. Find the original install location path and copy the install media to that same path
    1. The process below will fail if you run it from a different directory than the original install.
  2. Open a command prompt and go to the install directory and run
  3. start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine
    REBUILDDATABASE=1 SAPWD= REINSTALLMODE=omu

Use a random complex password for the SAPWD flag. It won’t be needed after master is restored from backup. The REINSTALLMODE=omu is needed if a service pack is installed. If this flag is left out the installer will fail because it tries to restore the original version of the master databas Open a command prompt and go to the SQL binary directory (D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn) and start SQL in single user mode (sqlservr.exe –m) then log into Management Studio Run script to restore database

  1. RESTORE DATABASE master FROM DISK = ‘L:\SQLBackups\Master0.bak’ WITH REPLACE;

Restart SQL server Restore model and MSDB like a normal database Restart SQL agent Restore user dbs

SQL Reporting Services Fails with “The Extension is not available to users.”

If you are unable to access your SQL Reporting web site and you receive the error below in the Windows Application Event Log simply restart IIS to resolve.

The Windows extension failed to read its confiuration settings from the RSReportServer.config file. The extension is not available to users.

How to Resolve “The Remote name could not be resolved:” When Connecting to Local SQL Reporting Services

If you have modified your machine name you may receive the error “Cannot connect to XXXXX”. “The remote name could not be resolved: ‘oldcomputername’ (Microsoft SqlServer.Management UI RSClient)”.

To resolve you need to edit reportserver.config. By default this file is at  C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer. Do a search for the old computer name and replace with with the current computer name or “localhost”.

Apr 6

Backing Up SQL 2005 Express Databases

Two of the features missing from SQL 2005 Express is support for SSIS and SQL Agent. Without these two features you are unable to setup maintenance plans to backup your SQL databases to disk. Luckily a user at webhostautomation.com developed a script that you can call from a batch file.

The script can be viewed herehttp://forums.webhostautomation.com/showthread.php?t=17198 .

Save the script as a .sql file and then use Windows Task Scheduler to call a batch file with the following text:

sqlcmd -S . -i “C:\Backup_All_Databases.sql”

Creating a Linked Server from SQL 2K5 64-bit to SQL 2K5 32-bit

I ran into an issue creating a linked server on SQL 2005 64-bit. I was trying to connect to a 2000 32-bit server. The error I received was:

OLE DB provider “SQLNCLI” for linked server “servername” returned message “Unspecified error”.

OLE DB provider “SQLNCLI” for linked server “servername” returned message “The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.”.

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “servername”. The provider supports the interface, but returns a failure code when it is used.

To fix this error you need to run the instcat.sql file on the 2000 instance. This file is in SP4. This file adds a missing stored procedure to the 2000 instance.

Renaming a Stand Alone SQL 2005 Server

The other week I ran into an issue where I had to rename the server that a SQL 2005 instance was installed on. It was a pretty simple process. First rename the server and reboot. As far as SQL goes all you have to do is update the sysservers table by running the stored procedures below.

For a renamed default instance, run the following procedures:
sp_dropserver
GO
sp_addserver, local
GO

For a renamed named instance, run the following procedures:
sp_dropserver
GO
sp_addserver, local
GO