Backup and Restore all databases and users SQL Server

Question

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.

Answer 1

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.