Restore-SqlDatabase

Reference

Module:: SQLServer

Restores a database from a backup or transaction log records.

Syntax

Restore-SqlDatabase
       [-ClearSuspectPageTable]
       [-KeepReplication]
       [-Partial]
       [-ReplaceDatabase]
       [-RestrictedUser]
       [-Offset <Int64[]>]
       [-RelocateFile <RelocateFile[]>]
       [-AutoRelocateFile]
       [-FileNumber <Int32>]
       [-RestoreAction <RestoreActionType>]
       [-StandbyFile <String>]
       [-StopAtMarkAfterDate <String>]
       [-StopAtMarkName <String>]
       [-StopBeforeMarkAfterDate <String>]
       [-StopBeforeMarkName <String>]
       [-ToPointInTime <String>]
       [-Database] <String>
       [-Path <String[]>]
       [[-BackupFile] <String[]>]
       [-SqlCredential <PSObject>]
       [-BackupDevice <BackupDeviceItem[]>]
       [-PassThru]
       [-Checksum]
       [-ContinueAfterError]
       [-NoRewind]
       [-Restart]
       [-UnloadTapeAfter]
       [-NoRecovery]
       [-DatabaseFile <String[]>]
       [-DatabaseFileGroup <String[]>]
       [-BlockSize <Int32>]
       [-BufferCount <Int32>]
       [-MaxTransferSize <Int32>]
       [-MediaName <String>]
       [-Script]
       [-AccessToken <PSObject>]
       [-TrustServerCertificate]
       [-HostNameInCertificate <String>]
       [-Encrypt <String>]
       [-ProgressAction <ActionPreference>]
       [-WhatIf]
       [-Confirm]
       [<CommonParameters>]

Restore-SqlDatabase
       [-ClearSuspectPageTable]
       [-KeepReplication]
       [-Partial]
       [-ReplaceDatabase]
       [-RestrictedUser]
       [-Offset <Int64[]>]
       [-RelocateFile <RelocateFile[]>]
       [-AutoRelocateFile]
       [-FileNumber <Int32>]
       [-RestoreAction <RestoreActionType>]
       [-StandbyFile <String>]
       [-StopAtMarkAfterDate <String>]
       [-StopAtMarkName <String>]
       [-StopBeforeMarkAfterDate <String>]
       [-StopBeforeMarkName <String>]
       [-ToPointInTime <String>]
       [-Database] <String>
       -ServerInstance <String[]>
       [-Credential <PSCredential>]
       [-ConnectionTimeout <Int32>]
       [[-BackupFile] <String[]>]
       [-SqlCredential <PSObject>]
       [-BackupDevice <BackupDeviceItem[]>]
       [-PassThru]
       [-Checksum]
       [-ContinueAfterError]
       [-NoRewind]
       [-Restart]
       [-UnloadTapeAfter]
       [-NoRecovery]
       [-DatabaseFile <String[]>]
       [-DatabaseFileGroup <String[]>]
       [-BlockSize <Int32>]
       [-BufferCount <Int32>]
       [-MaxTransferSize <Int32>]
       [-MediaName <String>]
       [-Script]
       [-AccessToken <PSObject>]
       [-TrustServerCertificate]
       [-HostNameInCertificate <String>]
       [-Encrypt <String>]
       [-ProgressAction <ActionPreference>]
       [-WhatIf]
       [-Confirm]
       [<CommonParameters>]

Restore-SqlDatabase
       [-ClearSuspectPageTable]
       [-KeepReplication]
       [-Partial]
       [-ReplaceDatabase]
       [-RestrictedUser]
       [-Offset <Int64[]>]
       [-RelocateFile <RelocateFile[]>]
       [-AutoRelocateFile]
       [-FileNumber <Int32>]
       [-RestoreAction <RestoreActionType>]
       [-StandbyFile <String>]
       [-StopAtMarkAfterDate <String>]
       [-StopAtMarkName <String>]
       [-StopBeforeMarkAfterDate <String>]
       [-StopBeforeMarkName <String>]
       [-ToPointInTime <String>]
       [-Database] <String>
       -InputObject <Server[]>
       [[-BackupFile] <String[]>]
       [-SqlCredential <PSObject>]
       [-BackupDevice <BackupDeviceItem[]>]
       [-PassThru]
       [-Checksum]
       [-ContinueAfterError]
       [-NoRewind]
       [-Restart]
       [-UnloadTapeAfter]
       [-NoRecovery]
       [-DatabaseFile <String[]>]
       [-DatabaseFileGroup <String[]>]
       [-BlockSize <Int32>]
       [-BufferCount <Int32>]
       [-MaxTransferSize <Int32>]
       [-MediaName <String>]
       [-Script]
       [-AccessToken <PSObject>]
       [-TrustServerCertificate]
       [-HostNameInCertificate <String>]
       [-Encrypt <String>]
       [-ProgressAction <ActionPreference>]
       [-WhatIf]
       [-Confirm]
       [<CommonParameters>]

Restore-SqlDatabase
       [-ClearSuspectPageTable]
       [-KeepReplication]
       [-Partial]
       [-ReplaceDatabase]
       [-RestrictedUser]
       [-Offset <Int64[]>]
       [-RelocateFile <RelocateFile[]>]
       [-AutoRelocateFile]
       [-FileNumber <Int32>]
       [-RestoreAction <RestoreActionType>]
       [-StandbyFile <String>]
       [-StopAtMarkAfterDate <String>]
       [-StopAtMarkName <String>]
       [-StopBeforeMarkAfterDate <String>]
       [-StopBeforeMarkName <String>]
       [-ToPointInTime <String>]
       [-DatabaseObject] <Database>
       [[-BackupFile] <String[]>]
       [-SqlCredential <PSObject>]
       [-BackupDevice <BackupDeviceItem[]>]
       [-PassThru]
       [-Checksum]
       [-ContinueAfterError]
       [-NoRewind]
       [-Restart]
       [-UnloadTapeAfter]
       [-NoRecovery]
       [-DatabaseFile <String[]>]
       [-DatabaseFileGroup <String[]>]
       [-BlockSize <Int32>]
       [-BufferCount <Int32>]
       [-MaxTransferSize <Int32>]
       [-MediaName <String>]
       [-Script]
       [-AccessToken <PSObject>]
       [-TrustServerCertificate]
       [-HostNameInCertificate <String>]
       [-Encrypt <String>]
       [-ProgressAction <ActionPreference>]
       [-WhatIf]
       [-Confirm]
       [<CommonParameters>]

Description

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 cmdlet generally correspond to properties on the Smo.Restore object.

Examples

PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.bak"

This command restores the full database MainDB from the file \\mainserver\databasebackup\MainDB.bak to the server instance Computer\Instance.

Example 2: Restore a database transaction log

PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.trn" -RestoreAction Log

This command restores the transaction log for the database MainDB from the file \\mainserver\databasebackup\MainDB.trn to the server instance Computer\Instance.

Example 3: Restore a database and prompt for a password

PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.bak" -Credential (Get-Credential "sa")

This command restores the full database MainDB from the file \\mainserver\databasebackup\MainDB.bak to the server instance Computer\Instance, using the sa SQL login. This command will prompt you for a password to complete the authentication.

Example 4: Restore a transaction log with the NORECOVERY option

PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.trn" -RestoreAction Log -NoRecovery

This command restores the transaction log of the database MainDB with the NORECOVERY option from the file \\mainserver\databasebackup\MainDB.trn to the server instance Computer\Instance.

Example 5: Restore transaction log records up to a point in time

PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.trn" -RestoreAction Log -ToPointInTime "Sep 21, 2017 11:11 PM"

This command restores the transaction log of the database MainDB up to the date passed to the ToPointInTime parameter, Sep 21, 2017 11:11 PM.

Example 6: Restore a database and relocate the data and log files

PS C:\> $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MainDB_Data", "c:\MySQLServer\MainDB.mdf")
PS C:\> $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("MainDB_Log", "c:\MySQLServer\MainDB.ldf")
PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.trn" -RelocateFile @($RelocateData,$RelocateLog)

This example restores the full database MainDB to the server instance Computer\Instance, and relocates the data and log files. For each file that is moved, the example constructs an instance of the Microsoft.SqlServer.Management.Smo.RelocateFile class. 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. The RelocateFile objects are passed to the RelocateFile parameter of the Restore-SqlDatabase cmdlet.

Example 7: Restore a database from tape

PS C:\> $TapeDevice = New-Object Microsoft.Sqlserver.Management.Smo.BackupDeviceItem("\\.\tape0", "Tape")
PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupDevice $TapeDevice

This example restores the database MainDB from the tape device named \\.\tape0 to the server instance Computer\Instance. To represent this device, the example constructs an instance of the Microsoft.Sqlserver.Management.Smo.BackupDeviceItem class. The constructor takes two arguments, the name of the backup device and the type of the backup device. This BackupDeviceItem object is then passed to the BackupDevice parameter of the Restore-SqlDatabase cmdlet.

Example 8: Restore a database from the Azure Blob Storage service

PS C:\> Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "https://mystorageaccountname.blob.core.windows.net/container/MyDB.bak" -SqlCredential "mySqlCredential"

This command restores the full database MainDB from the file on the Windows Azure Blob Storage service to the server instance Computer\Instance.

Example 9: Backup a database on SQL2016 and restore on SQL2017 using -AutoRelocateFile

PS C:\> Get-SqlDatabase -ServerInstance MYSERVER\SQL2016 -Database AdventureWorks | Backup-SqlDatabase -BackupFile 'C:\BAK2\AdventureWorks.bak'
PS C:\> $restore = Restore-SqlDatabase -ServerInstance MYSERVER\SQL2017 -Database AdventureWorks2016 -BackupFile 'C:\BAK2\AdventureWorks.bak' -AutoRelocateFile -PassThru
PS C:\> $db.RelocateFiles

LogicalFileName     PhysicalFileName
---------------     ----------------
AdventureWorks_Data C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\AdventureWorks_Data.mdf
AdventureWorks_Log  C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\AdventureWorks_Log.ldf

# You can see that the original PhysicalFileNames were indeed differet by running the followiong query:
PS C:\> Invoke-Sqlcmd -ServerInstance MYSERVER\SQL2016 -Query "RESTORE FILELISTONLY FROM  DISK = N'C:\BAK2\AdventureWorks.bak' WITH NOUNLOAD" | Select -Property LogicalName,PhysicalName

LogicalName         PhysicalName
-----------         ------------
AdventureWorks_Data C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\AdventureWorks_Data.mdf
AdventureWorks_Log  C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\AdventureWorks_Log.ldf

The first command takes a backup of database AdventureWorks on SQL2016 instance running on machine MYSERVER. The backup is saved under C:\BAK2.

The same backup file is used in the second cmdlet to restore the database on a SQL2017 instance running on the same machine (MYSERVER). Notice that without the -AutoRelocate switch, the cmdlet would have failed because physical files where different, as shown in the two tables above (furthermore, most likely the files would have been in use by SQL2016 and possibly not accessible by SQL2017). The -AutoRelocate allowed the user to avoid having to explicit use the -RelocationFile, the argument to which is quite tedious to build.

Note: there is an assumption that both the SQL2016 instance and SQL2017 instance have access to the C:\BAK2 folder.

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:	PSObject
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-AutoRelocateFile

When this switch is specified, the cmdlet will take care of automatically relocating all the the logical files in the backup, unless such logical file is specified with the RelocateFile. The server DefaultFile and DefaultLog are used to relocate the files.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-BackupDevice

Specifies 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 tape device.

Type:	BackupDeviceItem[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-BackupFile

Specifies the location or locations where the backup files are stored. This parameter is optional. If not specified, the default backup location of the server is searched for the name <database name>.trn for log restores, or <database name>.bak for all other types of restores. This parameter cannot be used with the BackupDevice parameter. If you are backing up to the Windows Azure Blob Storage service (URL), either this parameter or the BackupDevice parameter must be specified.

Type:	String[]
Position:	2
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-BlockSize

Specifies the physical block size, in bytes, for the backup. 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 for all other devices.

Type:	Int32
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-BufferCount

Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer. If there is insufficient virtual address space in the Sqlservr.exe process for the buffers, you will receive an out of memory error.

Type:	Int32
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Checksum

Indicates that a checksum value is calculated during the restore operation.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ClearSuspectPageTable

Indicates that the suspect page table is deleted after the restore operation.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type:	SwitchParameter
Aliases:	cf
Position:	Named
Default value:	False
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ConnectionTimeout

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

Type:	Int32
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ContinueAfterError

Indicates that the operation continues when a checksum error occurs. If not set, the operation will fail after a checksum error.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Credential

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

Type:	PSCredential
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Database

Specifies the name of the database to restore. This cannot be used with the DatabaseObject parameter. When this parameter is used, the Path, InputObject, or ServerInstance parameters must also be specified.

Type:	String
Position:	1
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

-DatabaseFile

Specifies the database files targeted by the restore operation. This is only used when the RestoreAction parameter is set to Files. When the RestoreAction parameter is set to Files, either the DatabaseFileGroups or DatabaseFiles parameter must also be specified.

Type:	String[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-DatabaseFileGroup

Specifies the database file groups targeted by the restore operation. This is only used when the RestoreAction parameter is set to File. When the RestoreAction parameter is set to Files, either the DatabaseFileGroups or DatabaseFiles parameter must also be specified.

Type:	String[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-DatabaseObject

Specifies a database object for the restore operation.

Type:	Database
Position:	1
Default value:	None
Required:	True
Accept pipeline input:	True
Accept wildcard characters:	False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:	String
Accepted values:	Mandatory, Optional, Strict
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-FileNumber

Specifies the index number that is used to identify the targeted backup set on the backup medium.

Type:	Int32
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-InputObject

Specifies the server object of the SQL Server instance where the restore occurs.

Type:	Server[]
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	True
Accept wildcard characters:	False

-KeepReplication

Indicates that the replication configuration is preserved. If not set, the replication configuration is ignored by the restore operation.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-MaxTransferSize

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

Type:	Int32
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-MediaName

Specifies the name that identifies a media set.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-NoRecovery

Indicates that the database is restored into the restoring state. A roll back operation does not occur and additional backups can be restored.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-NoRewind

Indicates that a tape drive is left open at the ending position when the restore is completed. If not set, the tape is rewound after the operation is completed. This does not apply to disk restores.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Offset

Specifies the page addresses to be restored. This is only used when RestoreAction is set to OnlinePage.

Type:	Int64[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Partial

Indicates that the restore operation is a partial restore.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-PassThru

Indicates that this cmdlet outputs the Smo.Backup object used to perform the restore operation.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Path

Specifies the path of the SQL Server instance on which to execute the restore operation. This parameter is optional. If not specified, the current working location is used.

Type:	String[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:	ActionPreference
Aliases:	proga
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-RelocateFile

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

Type:	RelocateFile[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ReplaceDatabase

Indicates that a new image of the database is created. This overwrites any existing database with the same name. If not set, the restore operation will fail when a database with that name already exists on the server.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Restart

Indicates that this cmdlet resumes a partially completed restore operation. If not set, the cmdlet restarts an interrupted restore operation at the beginning of the backup set.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-RestoreAction

Specifies the type of restore operation that is performed. Valid values are:

Database. The database is restored.
Files. One or more data files are restored. The DatabaseFile or DatabaseFileGroup parameter must be specified.
OnlinePage. A data page is restored online so that the database remains available to users.
OnlineFiles. Data files are restored online so that the database remains available to users. The DatabaseFile or DatabaseFileGroup parameter must be specified.
Log. The translaction log is restored.

Type:	RestoreActionType
Accepted values:	Database, Files, OnlinePage, OnlineFiles, Log
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-RestrictedUser

Indicates that access to the restored database is restricted to the db_owner fixed database role, and the dbcreator and sysadmin fixed server roles.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Script

Indicates that this cmdlet outputs a Transact-SQL script that performs the restore operation.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ServerInstance

Specifies the name of a SQL Server instance. This server instance becomes the target of the restore operation.

Type:	String[]
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	True
Accept wildcard characters:	False

-SqlCredential

Specifies an SQL Server credential object that stores authentication information. If you are backing up to Blob storage service, you must specify this parameter. The authentication information stored includes the Storage account name and the associated access key values. Do not specify this parameter for disk or tape.

Type:	PSObject
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-StandbyFile

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

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-StopAtMarkAfterDate

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

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-StopAtMarkName

Specifies the marked transaction at which to stop the recovery operation. This is used with StopAtMarkAfterDate to determine the stopping point of the recovery operation. The recoverd data includes the transaction that contains the mark. If the StopAtMarkAfterDate value is not set, recovery stops at the first mark with the specified name.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-StopBeforeMarkAfterDate

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

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-StopBeforeMarkName

Specifies the marked transaction before which to stop the recovery operation. This is used with StopBeforeMarkAfterDate to determine the stopping point of the recovery operation.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ToPointInTime

Specifies the endpoint for database log restoration. This only applies when RestoreAction is set to Log.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-UnloadTapeAfter

Indicates that the tape device is rewound and unloaded when the operation is completed. If not set, no attempt is made to rewind and unload the tape medium. This does not apply to disk backups.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type:	SwitchParameter
Aliases:	wi
Position:	Named
Default value:	False
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

Inputs

Microsoft.SqlServer.Management.Smo.Database

Microsoft.SqlServer.Management.Smo.Server[]

Specifies an SMO.Server object that describes the SQL Server instance on which the restore operation occurs.

System.String[]

Backup-SqlDatabase

Share via

Restore-SqlDatabase

Syntax

Description

Examples

Example 1: Restore a database from a backup file on a network share

Example 2: Restore a database transaction log

Example 3: Restore a database and prompt for a password

Example 4: Restore a transaction log with the NORECOVERY option

Example 5: Restore transaction log records up to a point in time

Example 6: Restore a database and relocate the data and log files

Example 7: Restore a database from tape

Example 8: Restore a database from the Azure Blob Storage service

Example 9: Backup a database on SQL2016 and restore on SQL2017 using -AutoRelocateFile

Parameters

-AccessToken

-AutoRelocateFile

-BackupDevice

-BackupFile

-BlockSize

-BufferCount

-Checksum

-ClearSuspectPageTable

-Confirm

-ConnectionTimeout

-ContinueAfterError

-Credential

-Database

-DatabaseFile

-DatabaseFileGroup

-DatabaseObject

-Encrypt

-FileNumber

-HostNameInCertificate

-InputObject

-KeepReplication

-MaxTransferSize

-MediaName

-NoRecovery

-NoRewind

-Offset

-Partial

-PassThru

-Path

-ProgressAction

-RelocateFile

-ReplaceDatabase

-Restart

-RestoreAction

-RestrictedUser

-Script

-ServerInstance

-SqlCredential

-StandbyFile

-StopAtMarkAfterDate

-StopAtMarkName

-StopBeforeMarkAfterDate

-StopBeforeMarkName

-ToPointInTime

-TrustServerCertificate

-UnloadTapeAfter

-WhatIf

Inputs

Related Links

Feedback

Additional resources