How to recover a SQL server to new file locations

I this example we have a recovered server where all databases is gone. This is often the situation if a server is fully restored from tape. To even get a chance to restore the databases, even the system databases you need to start SQL server. So to be able to proceed we need to have a master database in place. There is two way to solve this. Do a rebuild of the master database from the setup or copy a database file from some other system. My opinion is that it is often problem when you try to rebuild the master database so my favorite is to have a bunch of database files from all different version stored on my USB disk, include also the resource database (MSSQLSYSTEMRESOURCE).

First we have to start SQL server. Let’s put the files for master db in the location pointed out in the startup parameters for the service. If we start the SQL in normal way we certainly will get problem with the other system databases. The information where the other databases location is, reside in the master database and if this is changed or rebuilded the information will be incorrect.

To be able to start the SQL we need to start it in a mode where we tell it to not use any other databases then Master. To do this run from the command prompt sqlservr.exe  –C –F –T3608.

Now the server is started and you can now connect one session to it from Management Studio. Anyway the SQL server is up but in this situation we can´t restore the master database. To be able to restore it the server must be started in single user mode sqlservr.exe  –C –M. But this is not possible when the other systemdatabases not in place. So first we change the location to them so the SQL server can be able to start without the traceflagg.

When the server is started with  sqlservr.exe  –C –F –T3608 we can change the locations of the other system databases.

Resourcedatabase:
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'E:\ProgramFiles\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf');

Systemdatabases:
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf')
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf')

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\templog.ldf')

Now the locations are changed and the SQL server would be able to start with single user mode parameter sqlservr.exe  –C –M

Restart the service with the single user mode option and then do the restore of the master database. restore database master from disk = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master.bak' with recovery, replace
The Master is restored and probably the location now are correct for the systemdatabases if you put them in the original location before. So if we are lucky it will start ok and the other databases will come up as normal. If not restore them one by one.

CmdExec subsystem failed to load

I went in to problem when we did restore a SQL server. We didn´t know the original path of the installation so we just did as we faugth was ok.
Because of the need to restore also the MSDB database we get later get problem when execute CMD.exe from SQL Agent. Sense the path to the subcomponent in MSDB table sys.subsystems pointed to the old path. Just change is to the corrct one and it works perfect.

Have a nice day!

Joel

What says the state on logon fail Error 18456

Here is what the state means a litle more detailed.


2 and 5 -- Invalid userid
6 -- Attempt to use a Windows login name with SQL Authentication
7 -- Login disabled and password mismatch
8 -- Password mismatch
9 -- Invalid password
11 and 12 -- Valid login but server access failure
13 -- SQL Server service paused
18 -- Change password required

Problem connect to an Integration Service after SP install in SQL 2005

Many time I have had problem to connect to an Integration Service after SP 3 have been installed. The solution is to reregister the dll-s below.

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll

Unable to logon to Reporting Service with a domain account

I have a couple of times recived problem to logon to Reporting Service after I have done an installation. I have now read the installation manual and finds out that the account running RS need to be trusted for delegation. I have not known that before and I have done plenty of installations without this when I know there is no need for Kerberos. Today I get really frustrated and tried to solve why it was not possible to logon to the server. It worked ok with my local admin account but not the domain account with local admin rights. I run some other reporting instances on same server and they works well so I find it really strange. In BOL I find that you must be sure that the key rswindowsnegotiate is added in the RsReportServer.config file to enable Kerberos. Hmm why not remove this then to use NTML instead. I compared the working RS instance with the new one and it was not there in the working RS, so I just removed it and restarted the RS. This did magic, no I can logon to RS. Another solution should off couse be to setup the service account in a proper way but that is another question. So to make a RS work you have to setup the service account for RS to use Kerberos or be sure to remove the rswindowsnegotiate from RsReportServer.Config file

Check nr of virtual logfiles

As we know it is a good idéa to reduce the nr of virtuall logfiles in the transaction logg. To find out databases with many VLF you can use this small script.

CREATE TABLE #databases(
FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
)

CREATE TABLE #total(
Database_Name sysname
, VLF_count INT
, Log_File_count INT
)

EXEC sp_MSforeachdb N'Use [?];
Insert Into #databases
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #total
Select DB_Name(), Count(*), Count(Distinct FileID)
From #databases;
Truncate Table #databases;'

SELECT *
FROM #total
ORDER BY VLF_count DESC;
DROP TABLE #databases;
DROP TABLE #total;