SQL Server Backup and Restore

Performing backups and restores in TSQL.

BACKUP/RESTORE cannot access remote systems over mapped drives - use the UNC path.

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 MOVETO 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