locked
Moving a SQL database to a new SQL database server RRS feed

  • Question

  • Hello,

    We have a database (EPI_SUITE6) in SQL 2000 database. I am getting ready to move it to the SQL 2008 database in a new server. I have some questions.

    - I will have to create a database name "EPI_SUITE6" within the SQL 2008 database, won't I. I don't have the script to create the EPI_SUITE6 but can I create a database in general and then just name it is "EPI_SUITE6"?

    - Can I do a backup the current EPI_SUITE6 (full backup and log backup) and restore them to the new database in SQL 2008? Would it compatible between the 2000 version and 2008 version? If this is ok, then do I need any thing else? I mean index or any thing else to be restored beside the database and log file.

    Thanks


    Madison
    Thursday, October 20, 2011 6:41 PM

All replies

  • Madison

    I would recommend you to download Upgrade Adviser tool from MS site to identify potential errors you are may top get and then by using simple BACKUP/RESTORE commands restore the database on the new server. Make sure  you copy all logins/jobs/DTS and etc..


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, October 23, 2011 10:46 AM
  • Hi Madison, as Uri stated you need upgrade advisor to run across the database and a profiler trace of the calls made by the application to the database.  Also discuss the upgrade with your application vendor to ensure that they fully support their application on 2008.  Also make sure that all jobs, logins, etc exist on your new server, confirm passwords.

    This is also an excellent time to clean up your system, so identlfy all depricated objects, jobs etc and remove.

    One small gotcha, if you use SQL authenticaton, you will need to remap the users in the database to the logins you have created:

    http://msdn.microsoft.com/en-us/library/ms174378.aspx

    One other thing, are you going to change the compatability mode of the database?  You will need to do this to take advantage of the new features in SQL 2008.

    Hope this helps


    Henry Rooney www.wardyit.com Please leave feedback
    • Proposed as answer by Stephanie Lv Monday, October 24, 2011 11:32 AM
    Monday, October 24, 2011 4:43 AM
  • Hello Uri,

    Thank you so much for your help. I already downloaded the upgrade adviser file. I would assume that the upgrade adviser will need to be installed in the SQL 2008 (new place), isn't it.

    I'll have to re-create all logins/jobs and DTS from the new SQL 2008, won't I. I am not sure how it match with the current 2000 SQL.

    Thanks


    Madison
    Monday, October 24, 2011 2:13 PM
  • No, I have UA on old server, but I do not think it is necessarily 
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 24, 2011 2:18 PM
  • Hello Henry,

    I am going to install the upgrade adivsor in the new 2008 R2 server. I really have no idea at this point as how it works and used...

    No, we are using the window authentication.

    Can you explain to me about changing compatability mode of the database because I don't understand it?

    Thanks


    Madison
    Monday, October 24, 2011 2:19 PM
  • Hi,

    It's also a good idea to run DBCC UPDATEUSAGE, rebuild your indexes and update your statistics (rebuild index will update index statistics only)



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
    Monday, October 24, 2011 2:23 PM
  • Hi Andrew

    At this stage I thinkDBCC UPDATEUSAGE is less important



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 24, 2011 2:28 PM
  • Hi Madison, sorry for the late reply.  Changing the compatability mode will allow you to utilise all the features that are available in SQL 2008.  if you keep the database in 2000 compatability certain features will not be available, you can review the following article for more information:

    http://msdn.microsoft.com/en-us/library/bb510680.aspx

    I recommend updating the compatibility at or shortly after migration.


    Henry Rooney www.wardyit.com Please leave feedback

    Monday, February 13, 2012 12:34 AM