Share via

Move a database from one server to another server in SQL Server 2008

  • Article

There are many ways to move/copy a database from one server to another server using SQL Server Tools.

i) Detach the database from the old server and attach it in the new server. This is purely offline operation and it moves the database instead of copying it.

Refer https://msdn.microsoft.com/en-us/library/ms190209.aspx : how to use attach/detach database in SQL Server Management Studio(SSMS)

ii) Back up the database in the old server and restore it in the destination server. This can be performed during online and it creates a new database in the destination server.

Refer https://msdn.microsoft.com/en-us/library/ms187048.aspx for more information.

iii) Using copy database wizard in SQL Server Management Studio.

Select the database in the source server in SSMS
Right click ->Tasks->Copy Database Wizard to launch the copy database wizard.
Enter the source and destination credentials and select either attach/detach or SMO type
click next and you can schedule or run immediately
Click finish to execute it

Refer https://msdn.microsoft.com/en-us/library/ms188664.aspx for more information.

iv) The last type is to generate the create script using Generate Script Wizard (SSMS)  and execute it in the destination server.

Select the database in the source server in SSMS
Right click ->Tasks->Generate Scripts Wizard to launch the wizard.
Select the various scripting options needed and select the objects needed to generate the scripts for them. Make sure script data = true in the scripting option to generate script for data as well (INSERT statements)

click next ->next and finish to generate the script (new query window or clip board or file)
connect to the destination server and create the new database in it.
Click new query window and paste the script generated using GSW above and execute them with the destination database context.

Refer https://msdn.microsoft.com/en-us/library/ms181421.aspx for more information

v) Using Transfer Object in SMO

Sample code:
ScriptingOptions so = new ScriptingOptions();
so.ScriptData = true;
Transfer t = new Transfer(db);
t.CopyAllObjets = true;
t.options = so;
...................
..................
 t.TransferData();

Note: Transfer class is available in Microsoft.SqlServer.SmoExtendedClass.dll  (SQL Server 2008)
or Microsoft.SqlServer.Smo.dll (SQL Server 2005)

There are various member variables to be configurable. Also ScriptingOptions class object can be created and assigned to the transfer object as well.

set ScriptData = true in order to transfer data also. It copies the destination instead of moving the database

Refer  https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer.aspx for more information.

vi) You can make use of Database Publishing Wizard to accomplish this. You can specify the target version as SQL 2005 or SQL 2000 etc as per your requirement.

Comments

  • Anonymous
    August 25, 2011
    What about the logins? Do you know how can I manage do migrate the logins?? Thanks

  • Anonymous
    September 05, 2011
    Hello Matheus Thanks for the comment. In order to copy logins as well, you can use i) Copy Database Wizard approach by selecting the server side options in the wizard ii) SMO Transfer by specifying scripting options. In the other scenarios, logins are not copied. Thanks Sreekar

  • Anonymous
    September 14, 2011
    Check out this article from Microsoft on transferring logins. support.microsoft.com/.../918992

  • Anonymous
    February 14, 2012
    Goood Explantion

  • Anonymous
    July 09, 2012
    the origin database is on an english (language) server and should be copied to an german(language) server.... is it just backup and restore ?

  • Anonymous
    July 13, 2012
    Thanks. It solved my problem

  • Anonymous
    January 23, 2013
    Good work

  • Anonymous
    March 25, 2013
    This is a complete procedure to transfer database and logins from an istance to a new one, scripting logins and relocating datafile and log files on the destination. Everything using metascripts. zaboilab.com

  • Anonymous
    May 06, 2013
    jghjgh

  • Anonymous
    June 28, 2013
    This is nn update version of the complete procedure for massive database migration (login included and datafile relocation included) based on metascripts: zaboilab.com/.../massive-database-migration-between-sql-server-instances-the-complete-procedure

  • Anonymous
    November 13, 2013
    Very Helpful.!!

  • Anonymous
    November 18, 2013
    very helpful

  • Anonymous
    January 09, 2014
    good

  • Anonymous
    April 20, 2014
    great job......

  • Anonymous
    April 20, 2014
    Thanks a lot. Very good Explanation...

  • Anonymous
    May 08, 2014
    Is it possible or is it advisable to just copy the .mdf file to the destination server and restore? The same step  on option 2?

  • Anonymous
    August 18, 2014
    I have to migrate SQL Database server 2008 from one location to another location through Copying the database. Can anyone tell me the limitations & Restriction in this migration job.. Please

  • Anonymous
    September 04, 2014
    i have lost data file in database .i am trying to copy the data file another  same data base in another Instance then  data base will be UP or NOT ?is it possibile?

  • Anonymous
    April 10, 2015
    Can you help me transfer SQL data from my server to GoDaddy SQL server?  I talked to tech support, he said I have to use my SQL server capability to transfer and manage database to the IP address, username and password created of their server.  Has anybody done it?

  • Anonymous
    April 21, 2015
    Thanks for posting information with links and I found 1 more SharePoint move SQL DB files in this link msdn.microsoft.com/.../ms189133.aspx <a href="staygreenacademy.com/">SharePoint Training</a>

  • Anonymous
    June 03, 2015
    Hello, Is it possible to migrate or restore database running on MS SQL server R2 Standard edition SP3 to MS SQL server 2008 Enterprise edition SP3? Please advise and steps also. Thanks.