Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Move SQL Database to another Server

Posted on 2015-02-12
20
81 Views
Last Modified: 2015-03-04
I have been tasked to move SQL databases for Websense to another SQL server.  I have directions however, I'm not a DBA and have very little experience with this type of situation.  My question is... If I stop the required services on the current SQL server, Detach the databases from the current server then move them to the exact location on a different machine and then attach them will that be enough?
0
Comment
Question by:WellingtonIS
  • 8
  • 8
  • 3
  • +1
20 Comments
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 125 total points
ID: 40605674
You can detach a reattach but I would recommend backing up and restoring to the new database. What about SQL logins? Does it have any?
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 375 total points
ID: 40605692
Yes, backup & restore doesn't need the SQL Server service to be stopped. A part from logins you also need to verify if there are linked servers or jobs that may be needed in the new server.
0
 

Author Comment

by:WellingtonIS
ID: 40605719
Ok I did backup each individual database from SQL - I right clicked hit tasks and then backup.. I did this for all 6 of the databases, is this enough to restore using the same method then restoring?
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 375 total points
ID: 40605728
Yes, but be aware that the databases doesn't exist yet in the new server so you need to give them the same name in the restore screen.
0
 

Author Comment

by:WellingtonIS
ID: 40605732
Yes I do understand that much.  So basically I'm going to restore each database with the exact same name in the same folder as on the origional server?
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605733
Are the database servers of the same version? will both be running at the same time?

First transfer logins from the current to the new
Use below to add the two stored procedures, and then when you run, copy the non SA entries and paste them into the query for the new DB.
http://support.microsoft.com/kb/246133

If so, look into DB mirroring (backup and restore the database you want to transfer on the new server, make sure under options do not bring the DATAbase online.
Once the DB is restored, go through the SSMS and establish mirroring without a witness.
Once this is going, you control when/how the transition will and it should be seamless.

Coordinate the change at which point within the SSMS mirror properties for the database, you can trigger the failover from the old to the new.
30 seconds-minute transition. If anything goes wrong, you can always fail it back.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605736
Here is an MS link to Database mirroring:
https://msdn.microsoft.com/en-us/library/ms190941.aspx
0
 

Author Comment

by:WellingtonIS
ID: 40605743
No they will not.  I'm taking off the current server and moving it to a virual server.  I will disable  the origional SQL databases on the origional server. - The database servers are the same Windows 2008 r2 64 bit with SQL 2008.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605776
Real or Virtual, they only need to be running at the same time for a short duration until you transition. Once transitioned, you would use the system where the databases are active to break the mirroring setup. and that is it.

With mirroring, you can transition an application/database at a time with a way back without adverse impact on the users.  If the application is SQL server mirror aware, you can configure the application to handle the transition with minimal impact on the users/transparant.

If you do it backup/restore you would have to make sure that no one writes into the old DB while you are restoring on the new server or you could lose data. i.e. while you restore, a new record is added, another data is updated, etc.

What is the outage window for the transition? Is it for each DB/application or for all?
0
 

Author Comment

by:WellingtonIS
ID: 40605819
There's not many people right now  using this so it's minimal.  that's not my concern if I miss 10 mins.  I can stop the origional database.  I was able to restore the databases from the backups and I've added them to the correct location.  I have other issues and I think I'm going to have to get techinical support involved.  I know I'm missing steps for this.
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 375 total points
ID: 40605837
If you can do the migration when no one is using the database then it will be better. Then you can even stop the SQL Server and copy the data and log file to the new server (no need for backup & restore or detach & attach). Just keep in mind that you shouldn't start the SQL Server again so you be sure that everyone is connecting to the new server and not to the older one.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605842
The restore has to be done after you transfer the SQL logins.
http://support.microsoft.com/kb/246133

SQL server has two security sections, one for the Server deals with who is allowed to login (connect).
The second is for each database deals with access to the database.  

You can have access (connect rights) to the sql server, but no access to any database.
You can not have access to a database without having connect rights to the sql server.

If you do not transfer the logins, the databases while restored will be in a .........

Please look at my earlier post. The backup/restore is done right before the application is reconfigured to use the new server/instance
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605865
As I mentioned above, I find suggestions to copy/paste or move Db/log files especially to a person who says upfront that they are not a DBA highly risky given one need not be a DBA to handle such a transition.

a DPA deals with structure/performance adjustment of the database.

The other is that the person needs to rely on someone else to tell them whether the transition/move is successful ( I hear a broken phone situtaion) where the person who knows how to test the DB functionality is unable to connect if the logins were not transferred, and the person doing the transfer, says that the databases are online on the new server.

To the asker, what is the timeframe for completing this transition?
0
 

Author Comment

by:WellingtonIS
ID: 40605867
ok will check it out. Thanks.
0
 

Author Comment

by:WellingtonIS
ID: 40605873
ok I'm transfering from SQL 2008 to SQL 2008.  That script.  Just create it in the source machine?  I'm sorry I've never done this before. These are for server 2000 and 2005 I have 2008???
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605892
yes, you add the stored procedure to the master on the source system. you might as well add it to the new as well this way it will be there if needed for future transitions.

The output will be
create login username... SID password
.
.

make sure you DO not copy the create user SA from the original if present.

You need all the others. including windows groups, etc. you do not need oldserver referenced groups.
0
 

Author Comment

by:WellingtonIS
ID: 40605897
ok there are no groups this database is for recording Websense logs only.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605908
Sorry, looked, I think the sql2005 should work, but here is the link for sql 2008/2012 to transfer sql logins.

http://support.microsoft.com/kb/918992
0
 
LVL 77

Expert Comment

by:arnold
ID: 40605915
Covering all the possibilities.  There have to be logins referencing create login [olderserver\groupname for sql instances user, ....]

The important ones are the sql login users, passwords and SID.
The SID is what is set in the security section of each database and the rights this SID has on this database.
Without the transfer of logins, the security section of the DB will reflect a user, but will not have a matching Login Security. Creating one yourself will not do as the SIDs will not match .......
reseting/reattaching the newly created user to the restrored DB will mean you would need to know which role/permissions this user had or needs there.
0
 

Author Closing Comment

by:WellingtonIS
ID: 40644864
Basically what I did was a backup and a restore to the new virual server.  I created the same user name and password for the login account.  Everything went smoothly.  Thanks.  There was no need for any logs or passwords to be copied.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question