Scripting all of available databases to .sql files containing schema+data+users with single command

Question

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

• Kin Shah: 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.
• Armin: 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?
• Marian: 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.
• Kin Shah: @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.

Answer 1

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.

• Marian: You probably wanted to say "restore them", not "attach them", right?
• Dave Glassborow: Yep. Or alternative detach and reattach :D
• Armin: Thank you guys. I will follow your advise carefully and i will update this question soon.

Answer 2

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