current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question
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 Jan 29 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 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 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 Jan 30 at 15:02
add comment

2 Answers

up vote 3 down vote accepted

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.

I you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier version of SQL Server still works with 2008. The following will connect to a local SQL Server instance and create a DDL script for the 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.

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

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:

@ECHO ON

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%

SETLOCAL DISABLEDELAYEDEXPANSION
FOR /F %%L IN (%controlfile%%) DO (
  SET "line=%%L"
  SETLOCAL ENABLEDELAYEDEXPANSION
  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!
  ENDLOCAL
)
ENDLOCAL

net use m: /z
net use l: /z
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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