Backup \ Restore - passo a passo por linha de comando no SQL SERVER

 Backup por linha de comando

BACKUP DATABASE DB_BANCO TO DISK=''Caminhoparasalvar\nomedobanco_yyyymmdd.bak '

GO
 ---------------------------------------------------------------------------------------------------------------------------

Robocopy – caso necessário copiar o database para outro servidor

D:\Robocopy\robocopy.exe \\caminhodeorigem\ \\caminhodedestino\ /TEE /LOG+:diretoriodolog\nomedolog.log /COPYALL

---------------------------------------------------------------------------------------------------------------------------

 Restore por linha do comando

RESTORE DATABASE DB_BANCO FROM DISK = 'caminhoondeestasalvo\arquivodobackup.bak'

WITH REPLACE,
MOVE 'db_banco _log' TO 'diretorioondeestasalvodoarquivodelogparasobrescrever\nomedoarquivodelog.ldf',
MOVE 'db_banco' TO 'diretorioondeestasalvodoarquivomdfparasobrescrever\nomedoarquivodemdf.mdf'
GO
---------------------------------------------------------------------------------

-- Verificando ultima data de restore

declare @database varchar(50)

set @database = 'SEU_DATABASE'
SELECT rsh.destination_database_name AS [Database]
,rsh.user_name AS [Restored By]
,rsh.restore_date AS [Restore Started]
,bmf.physical_device_name AS [Restored From]
,rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs
ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf
ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id
where rsh.destination_database_name = @database
and rsh.restore_type = 'D'
ORDER BY rsh.restore_date DESC

 -----------------------------------------------------------------------------------

 Gera sincronismo usuários

use DB_BANCO
go
set nocount on
select 'use '+db_name()+char(10)+'go'
select 'set nocount on'
select 'select '''+name+''''+char(10)+
       'exec sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = '''+name+''''+char(10)+'go'
from sys.sysusers where uid > 4
and issqluser = 1
and sid not in (select sid from master..syslogins)
go
------------------------------------------------------------------------------