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.
-
If you'd like to do this without using third party utilities then have a read here: support.microsoft.com/kb/918992 and dbadiaries.com/… and dbadiaries.com/… – joeqwerty Jan 31 '15 at 16:46
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.