Upgrading SQL server 2008 to 2008R2 with too many

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Upgrading SQL server 2008 to 2008R2 with too many

Author

Topic

arminadmin
Starting Member

4 Posts

Posted - 2014-01-26 : 16:30:09

I have a SQL Server 2008 server on which i have hosted over 40 databases (It is a shared database server) Now i want to upgrade the SQL Server to 2008R2. The easiest way is to run installation media of 2008R2 and instead of installing a new instance of SQL Server, you will choose: "upgrade from sql 2008"

But as we all know this won't work and you will get UPGRADE failed message.

I read many many posts, articles and .... But i didn't find any complete reference. What i realized till now: The best way to do this upgrade is to retrieve USERS and Passwords of the 2008 server using this script: http://support.microsoft.com/kb/918992

And now i don't know how to script all of the databases and other needed items to be able to execute them on the 2008R2 instance.

My Goal is to restore all of the databases on the new 2008R2 server. Is there any command which create SCRIPT of all of available databases in one script?

Could you tell me how to do this upgrade? I think i need to script all of Permissions,Users and passwords and finally all of the databases Thank you guys for your help

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-27 : 08:01:50

An outline way to script out an instance is:

1. Script User Databases.


select 'DECLARE @DS nvarchar(512);' + CHAR(13) + CHAR(10)
	+ 'SET @DS = N''D:\ScriptOut\' + suser_sname(owner_sid) + '_backup.bak'';' + CHAR(13) + CHAR(10)
	+ 'BACKUP DATABASE ' + suser_sname(owner_sid) + ' TO DISK = @DS' + CHAR(13) + CHAR(10)
	+ 'WITH INIT, SKIP, CHECKSUM;' + CHAR(13) + CHAR(10)
	+ 'RESTORE VERIFYONLY FROM DISK = @DS;' + CHAR(13) + CHAR(10)
	+ 'GO' + CHAR(13) + CHAR(10)
from sys.databases
where database_id > 4;

2. Script db owners


select 'ALTER AUTHORIZATION ON DATABASE::' + suser_sname(owner_sid) + ';'
from sys.databases
where database_id > 4;

3 Script Login Defaults


select 'ALTER LOGIN ' + name + ' WITH DEFAULT_DATABASE = ' + default_database_name + ';'
from sys.server_principals
where type_desc like '%login'
	and default_database_name <> 'master';

4. Script Logins
If using the Microsoft code:
- make the DEFAULT_DATABASE = master for all logins.
- the logins will have thier original SID (security ID). This means you do not have to be concerned about permissions.
- get rid of any logins like sa.

5. Script Restores


select 'RESTORE DATABASE ' + suser_sname(owner_sid) + CHAR(13) + CHAR(10)
	+ 'FROM DISK = N''D:\ScriptOut\' + suser_sname(owner_sid) + '_backup.bak'';' + CHAR(13) + CHAR(10)
	+ 'WITH RECOVERY, REPLACE, CHECKSUM;' + CHAR(13) + CHAR(10)
	+ 'GO' + CHAR(13) + CHAR(10)
	+ 'USE ' + suser_sname(owner_sid) + CHAR(13) + CHAR(10)
	+ 'GO' + CHAR(13) + CHAR(10)
	+ 'DBCC checkdb;' + CHAR(13) + CHAR(10)
	+ 'GO' + CHAR(13) + CHAR(10)
	+ 'EXEC sp_updatestats;' + CHAR(13) + CHAR(10)
	+ 'GO' + CHAR(13) + CHAR(10)
from sys.databases
where database_id > 4;

6. Script Agent Jobs
Probably best just to do each one from Management Studio.
If using Maintenace Plans I would not bother but re-create them in the new instance.

You can then:
1. Backup the DBs on the old instance
2. Script Logins on new instance
3. Restore DBs on new instance
4. Script DB Owners on new instance. (ALTER AUTHORIZATION)
5. Script default dbs for logins on new instance. (ALTER LOGIN)
6. Script jobs on new instnace.

If you are not used to scripting you should test the scripts in a Test instance.
I always script out instances with something like the above before upgrading them.
Just as well when I had problems upgrading a SQL2005 instance!
(I eventually had to completly remove SQL Server including folders under Program Files and the registry tree)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190452

I would strongly recommend a recent server image before starting!

I have never had any problems upgrading SQL2008 to SQL2008R2 but it sounds as though something is not quite right with your setup.

Good Luck!

arminadmin
Starting Member

4 Posts

Posted - 2014-01-31 : 09:57:06

Man!
I think this is the best answer that i have got on web for my request
I really appreciate your care and time for this very clear and detailed answer on my question

arminadmin
Starting Member

4 Posts

Posted - 2014-02-01 : 05:32:02

Dear Ifor! I tried to do an in-place upgrade on a TEST VM and this is my experience and i will be really happy to have your help:

Hello there,
I'm trying to do an in-place upgrade of an Express instance of 2008 EXPRESS to Enterprise 2008R2.
I run the installation media and setup.exe and everything seems fine. At the end of UPGRADE i get a warning message which says:

WAIT ON THE DATABASE ENGINE RECOVERY handle failed when attempting to install Microsoft server 2008R2

I clicked OK and UPDATE progress continued till the end and the final result of update shows me that SQL ENGINE START failed.

When i open SQL Configuration i see that i can't Start SQL SERVER ENGINE and it is stopped.

I searched so much and there are lots of comments which says this is because of renaming SA account before the in-place upgrade.
Hopefully my SA account is not renamed or even disabled before in-place upgrade.
I read here :
https://www.teamdotnetnuke.com/index.php?/Knowledgebase/Article/View/358/20/error-wait-on-the-database-engine-recovery-handle-failed-when-attempting-to-install-microsoft-sql-server-2008-r2
It says to open Application data folder on C drive and locat Microsoft\protect folder and give system and ADMINUSER full permissions. ( i did it)

some said try to set another USER as sql startup user, i opened SQL configuration and i tried to change the user and i got this error:

WMI Provider error
The specified module could not be found [0x8007007e]

I tried the solutions: registered the following DLL

regsvr32 "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll"
Copy the file: "framedyn.dll" from folder: "C:\WINDOWS\system32\wbem" TO folder: "C:\WINDOWS\system32"

I tried to recompile sqlmgmproviderxpsp2up.mof

mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

After all of these when i tried to change SQL server user, i got:
WMI Console error: Access is denied. [0x80070005]

I tried to check "LIST FOLDER CONTENT" permissions for administrators and SQLSERVER user on the BINN folder within:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL

But problem is still the same.
According to the guide here : http://scottless.com/blog/archive/2010/12/07/sql-server-wmi-error-0x80041010-invalid-class.aspx
I tried to run the following command

cd C:\Program Files\Microsoft SQL Server\100\Shared\1033
Install localization information mofcomp sqlmgmprovider.mfl

At the moment SQL Server engine is stopped and i can't start it and i need your help. when i try to start it from the configuration, it says:
The request failed or the service did not respond in a timely fashion, Consult the event log or other applicable error logs for details.

EVENVIEWER logs has 4 warnings:
1. The SQL Server failed to initialize VIA support library [QLVipl.dll]. This normally indicates the VIA support library does not exist or is corrupted. Please repair or disable the VIA network protocol. Error: 0x7e.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

2. TDSSNIClient initialization failed with error 0x7e, status code 0x60. Reason: Unable to initialize the VIA listener. The specified module could not be found.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
3.TDSSNIClient initialization failed with error 0x7e, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The specified module could not be found.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
4.Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
5.SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Kind regards

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-02-01 : 09:22:27

I have just noticed some typos in my examples but they should at least give you an idea of what to do. (You will also need server roles, Broker bits etc if applicable.)

Your problems are way beyond me. Someone with more detailed installation knowledge might be able to help.

If you are confident that you have fully scripted the instance I would be inclined to try and completly remove SQL server and install from scratch. Be aware, this is a HIGH RISK strategy and you will have to be prepared to re-image the server, or install all the software from scratch, if things go wrong.

You should test restoring the instance on a different machine first.

arminadmin
Starting Member

4 Posts

Posted - 2014-02-02 : 11:30:13

Dear Ifor!
Thank you for your care. There is nothing wrong with the scripting databases method.

The above story is for an IN-PLACE upgrade of 2008EXPRESS to 2008R2ENTERPRISE
And i managed to fix the issue by Disabling VIA Protocol
Thank you for your guidance on scripting the databases

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources