sql 2005 express move to new machine running sql standard

Question

hello, I need to move over a bunch of databases on microsoft 2005 sql express to a new server that is running sql standard. I do not know were to begin. Please provide STEPS if possible, such as how do I restore on a new machine or how do Detach.

Answer 1

1) BACKUP DATABASE all user databases

2) RESTORE DATABASE on the new machine

---Script out also all needed logins

--SQL Logins:
SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
+ sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
+ sys.fn_varbintohexstr(sid) + ', '
+ 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
+ ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 
'us_english'))
+ ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 
'OFF' END
+ ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END
FROM sys.sql_logins
WHERE name<>'sa'
UNION ALL
--Windows logins:
SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
+ 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master'))
+ ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 
'us_english'))
FROM sys.server_principals
WHERE type IN ('U','G')
AND name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
AND name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
AND name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
AND name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM');

--Problem: when executing CREATE LOGINs generated by the query above,
--it is not possible to assign default database when that database is 
mirrored or unavailable for some other reason.
--(filled a bug for that, this needs to be solved)

--Server roles for logins:
SELECT 'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', ' + 
QUOTENAME(R.name)
FROM sys.server_principals L JOIN sys.server_role_members RM
ON L.principal_id=RM.member_principal_id
JOIN sys.server_principals R
ON RM.role_principal_id=R.principal_id
WHERE L.type IN ('U','G','S')
AND L.name NOT LIKE '%\SQLServer2005MSSQLUser$%$%'
AND L.name NOT LIKE '%\SQLServer2005SQLAgentUser$%$%'
AND L.name NOT LIKE '%\SQLServer2005MSFTEUser$%$%'
AND L.name NOT IN ('BUILTIN\Administrators', 'NT AUTHORITY\SYSTEM', 'sa');

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Here are my recommendations regarding miving databases: http://www.karaszi.com/SQLServer/info_moving_database.asp

---

Tibor Karaszi, SQL Server MVP | web | blog