Restore-SqlDatabase

Restores all or part of a database or the backups of transaction log records.
Restore-SqlDatabase [-Database*Object] <Database> [[-BackupFile] <string[]>] [-BackupDevice <BackupDeviceItem[]>][-BlockSize <int>] [-BufferCount <int>] [-Checksum] [-ClearSuspectPageTable] [-ContinueAfterError] [-DatabaseFile<string[]>] [-DatabaseFileGroup <string[]>] [-FileNumber <int>] [-KeepReplication] [-MaxTransferSize <int>][-MediaName <string>] [-NoRecovery] [-NoRewind] [-Offset <Int64[]>] [-Partial] [-Passthru] [-RelocateFile<RelocateFile[]>] [-ReplaceDatabase] [-Restart] [-RestoreAction <RestoreActionType>] [-RestrictedUser] [-Script][-StandbyFile <string>] [-StopAtMarkAfterDate <string>] [-StopAtMarkName <string>] [-StopBeforeMarkAfterDate<string>] [-StopBeforeMarkName <string>] [-ToPointInTime <string>] [-UnloadTapeAfter] [-Confirm] [-WhatIf][<CommonParameters>]
Restore-SqlDatabase [-Database*] <string> [[-BackupFile] <string[]>] -InputObject* <Server[]> [-BackupDevice<BackupDeviceItem[]>] [-BlockSize <int>] [-BufferCount <int>] [-Checksum] [-ClearSuspectPageTable][-ContinueAfterError] [-DatabaseFile <string[]>] [-DatabaseFileGroup <string[]>] [-FileNumber <int>][-KeepReplication] [-MaxTransferSize <int>] [-MediaName <string>] [-NoRecovery] [-NoRewind] [-Offset <Int64[]>][-Partial] [-Passthru] [-RelocateFile <RelocateFile[]>] [-ReplaceDatabase] [-Restart] [-RestoreAction<RestoreActionType>] [-RestrictedUser] [-Script] [-StandbyFile <string>] [-StopAtMarkAfterDate <string>][-StopAtMarkName <string>] [-StopBeforeMarkAfterDate <string>] [-StopBeforeMarkName <string>] [-ToPointInTime<string>] [-UnloadTapeAfter] [-Confirm] [-WhatIf] [<CommonParameters>]
Restore-SqlDatabase [-Database*] <string> [[-BackupFile] <string[]>] [-BackupDevice <BackupDeviceItem[]>][-BlockSize <int>] [-BufferCount <int>] [-Checksum] [-ClearSuspectPageTable] [-ContinueAfterError] [-DatabaseFile<string[]>] [-DatabaseFileGroup <string[]>] [-FileNumber <int>] [-KeepReplication] [-MaxTransferSize <int>][-MediaName <string>] [-NoRecovery] [-NoRewind] [-Offset <Int64[]>] [-Partial] [-Passthru] [-Path <string[]>][-RelocateFile <RelocateFile[]>] [-ReplaceDatabase] [-Restart] [-RestoreAction <RestoreActionType>][-RestrictedUser] [-Script] [-StandbyFile <string>] [-StopAtMarkAfterDate <string>] [-StopAtMarkName <string>][-StopBeforeMarkAfterDate <string>] [-StopBeforeMarkName <string>] [-ToPointInTime <string>] [-UnloadTapeAfter][-Confirm] [-WhatIf] [<CommonParameters>]
Restore-SqlDatabase [-Database*] <string> [[-BackupFile] <string[]>] -ServerInstance* <string[]> [-BackupDevice<BackupDeviceItem[]>] [-BlockSize <int>] [-BufferCount <int>] [-Checksum] [-ClearSuspectPageTable][-ConnectionTimeout <int>] [-ContinueAfterError] [-Credential <PSCredential>] [-DatabaseFile <string[]>][-DatabaseFileGroup <string[]>] [-FileNumber <int>] [-KeepReplication] [-MaxTransferSize <int>] [-MediaName<string>] [-NoRecovery] [-NoRewind] [-Offset <Int64[]>] [-Partial] [-Passthru] [-RelocateFile <RelocateFile[]>][-ReplaceDatabase] [-Restart] [-RestoreAction <RestoreActionType>] [-RestrictedUser] [-Script] [-StandbyFile<string>] [-StopAtMarkAfterDate <string>] [-StopAtMarkName <string>] [-StopBeforeMarkAfterDate <string>][-StopBeforeMarkName <string>] [-ToPointInTime <string>] [-UnloadTapeAfter] [-Confirm] [-WhatIf][<CommonParameters>]

The Restore-SqlDatabase cmdlet performs restore operations on a SQL Server database. This includes full database restores, transaction log restores, and database file restores. This cmdlet is modeled after the Microsoft.SqlServer.Management.Smo.Restore class. The parameters on this class generally correspond to properties on that Smo object.

Parameters
-BackupDevice <BackupDeviceItem[]>

The devices where the backups are be stored. This parameter cannot be used with the BackupFile parameter. Use this parameter if you are backing up to a non-disk medium such as tape.

-BackupFile <string[]>

The location or locations on disk where the backup files are stored. This is an optional parameter. If not specified, the default backup location of the server is searched for the name “databasename.trn” for log restores, or “databasename.bak” for all other types of restores. This parameter cannot be used with the BackupDevice parameter.

-BlockSize <int>

The physical block size for the backup, in bytes. The supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 (64 KB) bytes. The default is 65536 for tape devices and 512 otherwise.

-BufferCount <int>

The total number of I/O buffers to be used for the backup operation. You can specify any positive integer. Using many buffers might cause “out of memory” errors because of insufficient virtual address space in the Sqlservr.exe process.

-Checksum [<SwitchParameter>]

Calculates a checksum value during the restore operation. If not set, no checksum is calculated.

-ClearSuspectPageTable [<SwitchParameter>]

Deletes the suspect page table following the restore operation. If not set, no action will be taken towards the suspect page table.

-ConnectionTimeout <int>

The number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

-ContinueAfterError [<SwitchParameter>]

The operation continues when a checksum error occurs. When not set, the operation will fail after a checksum error.

-Credential <PSCredential>

A PSCredential object specifying the credentials for a SQL Server login that has permission to perform this operation.

-Database <string>

  • This value is required

The name of the database to be restored. Cannot be used with the -DatabaseObject parameter.When the -Database parameter is used, then the -Path, -InputObject, or -ServerInstance parameter must also be specified.

-DatabaseFile <string[]>

The database files targeted by the restore operation. Only used when the RestoreAction property is set to Files. When the RestoreAction parameter is set to Files, either the DatabaseFileGroups property or the DatabaseFiles property must be specified.

-DatabaseFileGroup <string[]>

The database file groups targeted by the restore operation. Only used when the RestoreAction property is set to File. When the RestoreAction parameter is set to Files, either the DatabaseFileGroups property or the DatabaseFiles property must be specified.

-DatabaseObject <Database>

  • This value is required
  • Accepts pipeline input ByValue

A database object. The restore operation will restore the backup to this database.

-FileNumber <int>

The index number used to identify the targeted backup set on the backup medium.

-InputObject <Server[]>

  • This value is required
  • Accepts pipeline input ByValue

The Server object of instance of SQL Server where the restore should occur.

-KeepReplication [<SwitchParameter>]

Preserves the replication configuration. If not set, the replication configuration is ignored by the restore operation.

-MaxTransferSize <int>

The maximum number of bytes to be transferred between the backup media and the instance of SQL Server. The possible values are multiples of 65536 bytes (64 KB), up to 4194304 bytes (4 MB).

-MediaName <string>

The name used to identify a particular media set.

-NoRecovery [<SwitchParameter>]

The database is restored into the restoring state. Roll back does not occur. Additional backups can be restored.

-NoRewind [<SwitchParameter>]

Leaves a tape drive open at the ending position when the restore completes. If not set, the tape is rewound after the operation completes. Does not apply to disk restores.

-Offset <Int64[]>

The page addresses to be restored. Only relevant when -RestoreAction is set to OnlinePage.

-Partial [<SwitchParameter>]

The restore operation is a partial restore.

-Passthru [<SwitchParameter>]

Outputs the Smo Backup object used to perform the backup operation.

-Path <string[]>

The path to the instance of SQL Server on which you want to execute the restore operation. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-RelocateFile <RelocateFile[]>

A list of Smo.Relocate file objects. Each object consists of a logical backup file name and a physical file system location. The restore moves the restored database into the specified physical location on the target server.

-ReplaceDatabase [<SwitchParameter>]

Creates a new image of the database. Overwrites any existing database with the same name. When not set, the restore operation will fail if a database with that name already exists.

-Restart [<SwitchParameter>]

Continues processing a partially completed restore operation. If not set (default), the cmdlet restarts an interrupted restore operation at the beginning of the backup set.

-RestoreAction <RestoreActionType>

  • Default value is Microsoft.SqlServer.Management.Smo.RestoreActionType.Database

The type of restore operation that should be performed. Valid options are Database, Files, Log, OnlinePage, OnlineFiles.Database: Restore the database.Files: Restore one or more data files. This requires the DatabaseFile or DatabaseFileGroup parameter to be set.Log: Restore the transaction log.OnlinePage: Restore a data page online while the database is still available to other users.OnlineFiles: Restore one or more data files while the database is still available to other users. This requires the DatabaseFile or DatabaseFileGroup parameter to be set.

-RestrictedUser [<SwitchParameter>]

Restricts database access to the restored database to the db_owner fixed database role, and the dbcreator and sysadmin fixed server roles.

-Script [<SwitchParameter>]

Outputs a Transact-SQL script that performs the task executed by this cmdlet.

-ServerInstance <string[]>

  • This value is required
  • Accepts pipeline input ByValue

The name of an instance of SQL Server. This server instance becomes the target of the restore operation.

-StandbyFile <string>

The name of an undo file that is used as part of the imaging strategy for an instance of SQL Server.

-StopAtMarkAfterDate <string>

Specifies the date to be used in conjunction with the mark name specified by the StopAtMarkName parameter to determine the stopping point of the recovery operation.

-StopAtMarkName <string>

Stops the recovery after a marked transaction. Used with the StopAtMarkAfterDate parameter to determine the stopping point of the recovery operation. Recovery includes the transaction that contains the mark. If the StopAtMarkAfterDate value is not set, recovery stops at the first mark with the specified name.

-StopBeforeMarkAfterDate <string>

Specifies the date to be used in conjunction with StopBeforeMarkName to determine the stopping point of the recovery operation.

-StopBeforeMarkName <string>

Stops the recovery before a marked transaction. Used with the StopBeforeMarkAfterDate to determine the stopping point of the recovery operation.

-ToPointInTime <string>

The endpoint for database log restoration. Only applies when the -RestoreAction parameter is set to Log.

-UnloadTapeAfter [<SwitchParameter>]

The tape device is rewound and unloaded when the operation completes. When not set, no attempt is made to rewind and unload the tape medium. Does not apply to disk backups.

-Confirm [<SwitchParameter>]

Prompts you for confirmation before executing the command.

-WhatIf [<SwitchParameter>]

Describes what would happen if you executed the command without actually executing the command.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Inputs

Smo.Server

A SMO.Server object referring to the instance of SQL Server on which the restore operation should occur.

Examples
  1. This command restores the full database ‘MyDB’ from the file ‘\sharefolderMyDB.bak’ to the server instance ‘computerInstance’:
    C:PS> Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile "\sharefolderMyDB.bak"
    
  2. This command restores the transaction log of the database ‘MyDB’ from the file ‘\sharefolderMyDB.trn’ to the server instance ‘computerInstance’:
    C:PS> Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile "\sharefolderMyDB.trn" -RestoreAction Log
    
  3. This command restores the full database ‘MyDB’ from the file ‘\sharefolderMyDB.trn’ to the server instance ‘ComputerInstance’, using the sa SQL credential:
    C:PS> Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile "\sharefolderMyDB.bak" -Credential (Get-Credential sa)
    

    This command will prompt you for a password to complete SQL authentication.

  4. This command restores the transaction log of the database ‘MyDB’ using the NORECOVERY option from the file ‘\sharefolderMyDB.trn’ to the server instance ‘ComputerInstance’:
    C:PS> Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile "\sharefolderMyDB.trn" -RestoreAction Log -NoRecovery
    
  5. This command restores the transaction log of the database ‘MyDB’ up to the date passed to the -ToPointInTime parameter, ‘Nov 11, 2011 11:11 AM’:
    C:PS> Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile "\sharefolderMyDB.trn" -RestoreAction Log -ToPointInTime "Nov 11, 2011 11:11 AM"
    
  6. This example restores the full database ‘MyDB’ to the server instance ‘ComputerInstance’, and relocates the data and log files:
    C:PS> $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MyDB_Data", "c:MySQLServerMyDB.mdf")
    $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MyDB_Log", "c:MySQLServerMyDB.ldf")
    Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile "\sharefolderMyDB.trn" -RelocateFile @($RelocateData,$RelocateLog)
    

    For each file that is to be moved, the example constructs an instance of the Microsoft.SqlServer.Management.Smo.RelocateFile: ‘$RelocateData’ and ‘$RelocateLog’. Each constructor takes two arguments: the logical name of the file and the physical location where the file will be placed on the target server. These RelocateFile objects are passed to the -RelocateFile parameter of the Restore-SqlDatabase cmdlet.

  7. This example restores the database ‘MyDB from the tape device named ‘\.tape0” to the server instance ‘computerInstance’:
    C:PS> $tapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\.tape0", "Tape")
    Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupDevice $tapeDevice
    

    To represent this device, the example constructs an instance of the Microsoft.Sqlserver.Management.Smo.BackupDeviceItem. The constructor takes two arguments: the name of the backup device (‘\.tape0’) and the type of the backup device (‘Tape’). This BackupDeviceItem object is then passed to the -BackupDevice parameter of the Restore-SqlDatabase cmdlet.

Additional Notes
 
Related Links