I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance.

As i'm told it is better to script the databases and run the script on the new instance.

I can do this in 2 ways: 1. Running a script on the 2008 server to get all of users and passwords and user mapping to databases and then Script all of the databases (without users information) and then restore databases on new instance and then run the users script for logings and users mapping to databases. i have been told it is a better way

-2: Script all of the databases using Script to WIZARD and check-mark DATA+SCHEMA+USERS and then script the database to .SQL file and run this SQL script on the new instance.

What is your suggestions? Which is the best way to this without facing any issue. Kind regards

  • 2
    Backup restore is the way to go. Check out How to move a database and Upgrade SQL Server databases. They do apply to your situation and will give you more thoughts on how to better plan. Dont go for option 2 as depending on the database sizes, the scripts will be huge and difficult to manage. – Kin Shah Jan 29 '14 at 14:10
  • Dear Kin! Thank you for your comment. But i have heard so much that BACK UP and RESTORE can cause compatibility issues and on some cases restore may be not well done. I'm told that scripting is 100% accurate and i will not face any issue on this way. Don't you have the same opinion? – Armin Jan 29 '14 at 17:44
  • 1
    Backup and restore is the preferred way to move a complete database, if it's possible. The problem is with restoring to a lower version server (from 2008 R2 to 2008..or something similar), because it's not possible. Please read the questions @Kin has kindly pointed you to. – Marian Jan 30 '14 at 9:02
  • @armin Agreed with Marian. Who ever told you that is not correct to script out database and then run the script to migrate. I would even not recommend detach/attach as if something goes wrong, you are left without a backup. please go through the links that I have provided and let me know if you still need help. Go for bqckup/restore. – Kin Shah Jan 30 '14 at 15:02

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use

EXEC sp_change_users_login 'Auto_Fix', 'username'

for fixing user accounts.

The Wizard approach sounds doable.

If you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier versions of SQL Server still works with 2008. The following will connect to a local SQL Server instance and create a DDL script for my_database:

scptxfr /s .\SQLExpress /d my_database /I /f database_script.sql /O /H

Without programming, it is going to be difficult to get all the data out in scripts.

  • 1
    You probably wanted to say "restore them", not "attach them", right? – Marian Jan 29 '14 at 13:32
  • Yep. Or alternative detach and reattach :D – Dave Glassborow Jan 29 '14 at 14:38
  • Thank you guys. I will follow your advise carefully and i will update this question soon. – Armin Feb 1 '14 at 10:27

I would probably just detach/attach via script.

Drop a list of database names into a file ("control.txt"), which you can get from sys.databases (removing the system databases).

The script itself is:


set newipmdf=\\newserver\drive$
set newipldf=\\newserver\drive$
set controlfile=control.txt
set oldserver=oldserver\instance
set oldmdfpath=d:\path
set newmdfpath=d:\path
set copymdfpath="m:\path"
set newserver=newserver\instance
set oldlogpath=e:\path
set newlogpath=e:\path
set copylogpath="l:\path
set movedmdfpath=%oldmdfpath%\moved
set movedldfpath=%oldlogpath%\moved

mkdir %movedmdfpath%
mkdir %movedldfpath%

net use m: %newipmdf%
net use l: %newipldf%

FOR /F %%L IN (%controlfile%%) DO (
  SET "line=%%L"
  ECHO !line!
  sqlcmd -E -S!oldserver! -Q"EXEC master.dbo.sp_detach_db @dbname = N'!line!'"
  copy "!oldmdfpath!\!line!.mdf" !copymdfpath!
  copy "!oldlogpath!\!line!_log.ldf" !copylogpath!
  sqlcmd -E -S!newserver! -Q"CREATE DATABASE [!line!] ON ( FILENAME = N'!newmdfpath!\!line!.mdf' ),( FILENAME = N'!newlogpath!\!line!_log.ldf' ) FOR ATTACH"
  move "!oldmdfpath!\!line!.mdf" !movedmdfpath!
  move "!oldlogpath!\!line!_log.ldf" !movedldfpath!

net use m: /z
net use l: /z

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.