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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
You have two options. First is backup copy and restore and second is deattach and attach database file.

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.
MarkAuthor Commented:
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?
Deepak ChauhanSQL Server DBACommented:
In this case you can use  "sp_help_revlogin" stored procedure provided by microsoft specifically for migration. Here is step by step info and in your case Method 3 is useful.


execute the sp_help_revlogin SP in master database and it will generate the create login script for you then copy the script and execute on target server.

Because users are mapped with the login sid and this SP generate login script with SID so rare chances of orphan users. But you can run below script to check the orphan users.

use <database>
exec sp_change_users_login 'report'

If orphan users exist in the database
you have to run this script to remap.

use <database>
exec sp_change_users_login 'Auto_fix', '<username>'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MarkAuthor Commented:
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.
MarkAuthor Commented:
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'."
Deepak ChauhanSQL Server DBACommented:
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.
MarkAuthor Commented:
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.
MarkAuthor Commented:
excellent instructions on porting logins!!! Worked great.
Deepak ChauhanSQL Server DBACommented:
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.
MarkAuthor Commented:
Nope, not there:
Server Roles
Deepak ChauhanSQL Server DBACommented:
Oh... I can recall there was issue with Sevice pack and SSMS, not exposing the Public role. but no impact.

Thanks no worry.
Deepak ChauhanSQL Server DBACommented:
This was only in SQL server 2005.
MarkAuthor Commented:
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?
Deepak ChauhanSQL Server DBACommented:
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.

MarkAuthor Commented:
Applied service packs and server role 'public' now appears!
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.