SQL Server Backup and Restore

Performing backups and restores in TSQL.

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

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'

• 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:

  RESTORE FILELISTONLY 
  FROM DISK = N'\\servername\folder\BackupFile.bak'

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 here.

To map a database user to a server login, use the ALTER USER statement:

ALTER USER [username] WITH LOGIN = [loginName]

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