====== SQL Server Backup and Restore ======
Performing backups and restores in TSQL.

===== Running BACKUP =====
<WRAP center round tip 60%>
''BACKUP''/''RESTORE'' cannot access remote systems over mapped drives - use the UNC path.
</WRAP>

<code tsql>BACKUP DATABASE [DbName]
TO DISK = N'\\servername\folder\BackupFile.bak'
WITH 
  NOFORMAT
, INIT
, NAME = 'DbName backup'
, SKIP
, NOREWIND
, NOUNLOAD  
, STATS = 100
</code>

===== Running RESTORE =====
<code tsql>RESTORE DATABASE [DbName]
FROM DISK = N'\\servername\folder\BackupFile.bak'
WITH
  MOVE 'DbName_Data' TO 'E:\Data\DbName_201311172030.mdf'
, MOVE 'DbName_Logs' TO 'E:\Logs\DbName_201311172030.ldf'
</code>
  * You should ''MOVE''...''TO'' the appropriate instance-specific location for data and logfiles.
  * Timestamping the restore files is a convenient habit; it's clear when the restore was done and you avoid collisions between files named solely for the associated database.
  ** You'll need a ''MOVE'' clause for each logical file in the backup. If you don't know these, use:
  .. <code tsql>RESTORE FILELISTONLY 
FROM DISK = N'\\servername\folder\BackupFile.bak'
</code>

==== Restoring backups from one instance onto another ====
SQL (i.e. non-Windows) users won't make sense in a backup of one instance restored to another:
  * database users on the source server may have no corresponding login name on the target;
  * even if a corresponding login name exists, database users are mapped to server principal IDs rather than login names.

Instructions for transferring login names between servers (including stored passwords where applicable) are available [[http://support.microsoft.com/kb/918992|here]].

To map a database user to a server login, use the ''ALTER USER'' statement:
<code tsql>ALTER USER [username] WITH LOGIN = [loginName]</code>

===== Monitoring progress =====
<code tsql>WITH cte AS (
  SELECT
    SUBSTRING(s.[text], r.statement_start_offset/2, 255) AS SqlStatement
  , r.start_time
  , DATEDIFF(MILLISECOND, start_time, GETDATE()) AS ms_elapsed
  , NULLIF(r.estimated_completion_time, 0) AS ms_remaining
  , NULLIF(r.percent_complete, 0) AS pc_complete
  
  FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
  WHERE r.command LIKE 'RESTORE %'
  OR r.command LIKE 'BACKUP %'
)
SELECT
  SqlStatement
, start_time AS StartedDateTime
, DATEADD(MILLISECOND, ms_remaining, start_time) AS ExpectedCompletionDateTime
, ROUND(pc_complete, 1) AS [% complete]
FROM cte</code>

{{tag>tsql backup restore running}}