Mark
asked on
SQL Server 2005, how to move entire server instance
We have 32 bit SQL Server 2005 running on an old 32bit Server 2007 which started out as part of a turn-key system. Over time, the turn-key system has been discontinued and we've developed new databases and tables for use in the replacement system. See attached image -- all the databases beginning with HPRS are for the new system (really only HPRS, the others are yearly archives), and all the other databases (OSH*, ST, SYS) are from the legacy turn-key system.
The time has come to moth-ball the legacy software and databases. What I'd like to do is clone-off the entire TRAVERSE105 instance, databases, tables, users, privileges, server agent jobs, ... everything, to another computer. I would then remove the new databases from that copied instance stripping it down to just the legacy databases -- for backup reference in case we ever need to look anything up. I would then be free to likewise remove the legacy databases from the production system and ultimately upgrade it to 64bit SQL Server 2014.
Is there a way to do this?
DBinstance.jpg
The time has come to moth-ball the legacy software and databases. What I'd like to do is clone-off the entire TRAVERSE105 instance, databases, tables, users, privileges, server agent jobs, ... everything, to another computer. I would then remove the new databases from that copied instance stripping it down to just the legacy databases -- for backup reference in case we ever need to look anything up. I would then be free to likewise remove the legacy databases from the production system and ultimately upgrade it to 64bit SQL Server 2014.
Is there a way to do this?
DBinstance.jpg
ASKER
Backing up and restoring databases is no problem -- done that a'plenty. My big issue is getting the logins, permission and user mappings. SSIS can do this? How is this accessed from SQL Server Management Studio?
I'll check into SSIS -- can you recommend a tutorial?
I'll check into SSIS -- can you recommend a tutorial?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome! That is an outstanding tip! I've run the script and created the userlogin script on the original computer. The output script looks good. I don't have SQL Server installed on the new machine yet, but started on that today. Will get that up and migrate databases per your initial suggestions. Will post back with results.
ASKER
So far, so good. Installed SQL Server, restored the databases, created and ran the sp_change_users_login. Logins and all the server roles and user mappings came over as well, except for the Server Role 'public' (see red arrow in attached image). However, the 'public' database role seems intact (see highlighted database role on image).
I assume the server role and database role are different things, though named alike? Any idea what the 'public' server role would be for? -- see 2nd image for its properties. How would I create this? All users have this set as a server role on the original system. I tried:
create role public
and got the error "Incorrect syntax near the keyword 'public'."
ServerRoles-DatabaseRoles.jpg
publicPropertes.jpg
I assume the server role and database role are different things, though named alike? Any idea what the 'public' server role would be for? -- see 2nd image for its properties. How would I create this? All users have this set as a server role on the original system. I tried:
create role public
and got the error "Incorrect syntax near the keyword 'public'."
ServerRoles-DatabaseRoles.jpg
publicPropertes.jpg
You dont need to create role "Public" this is the default role and every login created in server is the member of this role by default. If you will check any login property in the security folder you will see this role is assigned to every login. So no worry about this.
Yes server and database level roles are different.
Members users of database level role are limited to only database in which they exists.
Member of server level role can do server level activity.
Like Sysadmin -- can do anything on the server instance.
DBCreater -- can create database on the server instance.
Yes server and database level roles are different.
Members users of database level role are limited to only database in which they exists.
Member of server level role can do server level activity.
Like Sysadmin -- can do anything on the server instance.
DBCreater -- can create database on the server instance.
ASKER
Thanks for the clarification. I'll remember that. Odd that the original instance does have a 'public' server role, but as you say, doesn't seem to matter with the testing I've done so far.
ASKER
excellent instructions on porting logins!!! Worked great.
Good to know you are done with testing and successfull move.
But seems here is still some confusion which i would like to clarify .
I said you can not create Public server role because this role created by default in sql server.
Do one thing
connect New SQL server instance
expand Security Folder
expand Server Roles folder
Now you can see Public role ordinal position 5.
But seems here is still some confusion which i would like to clarify .
I said you can not create Public server role because this role created by default in sql server.
Do one thing
connect New SQL server instance
expand Security Folder
expand Server Roles folder
Now you can see Public role ordinal position 5.
ASKER
Nope, not there:
Oh... I can recall there was issue with Sevice pack and SSMS, not exposing the Public role. but no impact.
Thanks no worry.
Thanks no worry.
This was only in SQL server 2005.
ASKER
Well, good point. This is a new SQL Server 2005 install on XP (because I am mothballing this legacy package that ran on XP). However, because both XP and SQL Server 2005 are no longer supported, my SQL Server 2005 installation is fresh off the CD, no updates. Is there a way to still get updates for SQL Server 2005?
You can download Service pack 3 or SP4. Just to inform you Microsoft is going to discontinue the support from 12th April 2016. So no further updates or SPs.
SP3.
http://www.microsoft.com/en-in/download/details.aspx?id=14752
SP4
http://www.microsoft.com/en-in/download/details.aspx?id=7218
SP3.
http://www.microsoft.com/en-in/download/details.aspx?id=14752
SP4
http://www.microsoft.com/en-in/download/details.aspx?id=7218
ASKER
Applied service packs and server role 'public' now appears!
Thanks again for your expert help!! I got the mission accomplished quickly.
Just to inform you Microsoft is going to discontinue the support from 12th April 2016.No worries. This is exactly why I am moving the legacy tables off our main DB server and onto this XP device (which is where the legacy VBA application runs best). We have one or two more modules to migrate off the old program, then we can hopefully power off this old beast and I can upgrade the production server to SQL Server 2014!
Thanks again for your expert help!! I got the mission accomplished quickly.
Your screenshot is showing most of the databases are in readonly mode. so you can take the backup of these databases any time and restore them to destination server.
For read - write databases you can take full backup first and restore them on Destination server in no recovery mode, then take the differential backup on source server and restore on destination with recovery.
Option 2:
Offline the database and copy the MDF and LDF file and attach these files to destination server.
Next step:
you have to script out all logins and create on the destination server.
Script out the job and create on the destination server.
Remap the SQL login with database users.
You can do login and job movement using SSIS in one go.