CODEWITHSUNDEEP
sql-serversql-server-2014
PJD
PJD

Reputation: 775

New SQL Server Installation

It has been a long time since I have had to do this but wanted some re-assurance. I am currently moving our SQL Server 2014 Standard edition environment over to a new server. The installation on the new Server is still exactly the same. I have moved the Reporting Databases across already and all good.

I now need to move the master/model/msdb/temp_db. I am having to use the backup and restore process due to the old server being in a production environment so limited on down time.

If I back up each of the databases master/model/msdb and restore them over the one's that were set up after install, is it likely I will have any issues? If I remember correctly this should then give me all the original SQL Accounts that I had set up on the old server.

I know temp_db cannot be moved in this fashion but I would of thought perhaps wrongly that I really don't need to move this database over from the older server to the new one?

At the end of the day I just want a carbon copy of my old environment and after some advice/reassurance before doing anything.

Thanks

Upvotes: 0

Views: 67

Answers (1)

sepupic
sepupic

Reputation: 8697

If I back up each of the databases master/model/msdb and restore them over the one's that were set up after install, is it likely I will have any issues? If I remember correctly this should then give me all the original SQL Accounts that I had set up on the old server. Master database has paths to all your user databases. If you just restore master on the new server and no user databases will be found on the new server and old paths, no database except for system databases will be online.

If you have any other user database on the new server (you said you already moved Reporting Databases?) it will be successfully removed.

After master's restore you'll see only old databases that resides on the old server.

But all the logins will be there, this is correct. Even if logins are not a problem, you can always move them to another server using revlogin ot Transfer logins SSIS task.

I know temp_db cannot be moved in this fashion but I would of thought perhaps wrongly that I really don't need to move this database over from the older server to the new one?

Tempdb should not be moved, it's created automatically at every server restart.

Upvotes: 1

Related Questions