setup.rll is either not designed to run on Windows or it contains an error.

Hi SQL folks. A couple of time when I have done installation of SQL 2008 R2 with unpacked iso image the database engine installation has failed. And it tells you that some of the msi image is corrupt. Im nut sure if there is any problem when unpacking the iso image. Anyway after this has happened it’s not possible to uninstall the corrupt SQL installation. You will receive an error like this:

Application popup: setup.exe - Bad Image : C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\resources\1033\setup.rll is either not designed to run on Windows or it contains an error. Try installing the program again using the original installation media or contact your system administrator or the software vendor for support.

First time I saw this I did not solve it. So I have to let the windows guys reinstall the operating system. But today when same error come up googled the error message and saw that Alex Siow solved it just with copying the setup.rll files from the installation media path: 1033_ENU_LP\x64\Setup\sqlsupport_msi\PFiles\SqlServr\100\Setup\fe72iemr\Res\1033 to Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Resources\1033 and it also worked for me. Yes!

Have a nice day everyone.

How to move a database to a lower version level

It easier than you think.
1. In the source SQL server you right klick on the database you wich to move or copy. Chose “Tasks”, then “Generate Scripts.." Run this wizart to export all objects in the database. Unde advanced chose to save it for SQL 2005 or whatever version it is.
2. On the destination server run the script to create the database and all objects in it.
3. Now we have an empty database. Let’s then export import the data. For this we use the BCP utility. On the source server run this script in a query window.(thanks for this script Madhivanan)


declare @tables table(table_name varchar(100))
insert into @tables
select name from sysobjects where xtype ='u'
declare @table_name varchar(1000)
select @table_name=min(table_name)from @tables
while @table_name>''
Begin  
Declare @str varchar(1000)    
set @str='Exec Master..xp_Cmdshell ''bcp '+db_name()+'.dbo.'+@table_name+' out C:\temp\test\'+@table_name+'.txt -T -c'''
Exec(@str)  
select  @table_name=min(table_name) from @tables where table_name>@table_name
print @str
End


4. Now we export all the data to textfiles. Every table has a textfile each. Let’s import them now.
5. On the target server run same scrip again but change out to ín instead. Be sure to use the correct path to the files also.
That’s all, easy and quite fast way to go.
Joel Sjöö

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