2

Is there a way to backup and restore an entire SQL Server in one command (Databases, Data and Users)? I've successfully achieved this with out MySql server but can only find examples of single database exports for SQL Server.

2

To steal from one of my own answers, :) I've successfully used this script to migrate hundreds of databases. (This script requires network connectivity between the servers.)

@ECHO ON

set controlfile=control.txt
set oldserver=
set oldmdfpath=
set newmdfpath=
set newserver=
set oldlogpath=
set newlogpath=
set movedmdfpath=
set movedldfpath=

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" !newmdfpath!
  copy "!oldlogpath!\!line!_log.ldf" !newlogpath!
  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

(The file control.txt contains a list of the databases to be moved. You can get those via select name from sys.databases where name not in ('master','msdb','model','tempdb').)

Backup/restore is the safest method, but detach/attach is a lot faster when you get a really large number of databases.

You'll have to deal with logins (and possible sid issues) separately. This link might help. Ditto JoeQwerty's links above.

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.