Restore‑SqlDscDatabase

SYNOPSIS

Restores a SQL Server database from a backup file.

SYNTAX

ServerObject (Default)

Restore-SqlDscDatabase -ServerObject <Server> -Name <String> -BackupFile <String> [-RestoreType <String>]
 [-NoRecovery] [-Standby <String>] [-ReplaceDatabase] [-RelocateFile <RelocateFile[]>] [-Checksum]
 [-RestrictedUser] [-KeepReplication] [-FileNumber <Int32>] [-ToPointInTime <DateTime>]
 [-StopAtMarkName <String>] [-StopAtMarkAfterDate <DateTime>] [-StopBeforeMarkName <String>]
 [-StopBeforeMarkAfterDate <DateTime>] [-BlockSize <Int32>] [-BufferCount <Int32>] [-MaxTransferSize <Int32>]
 [-Refresh] [-Force] [-PassThru] [-WhatIf] [-Confirm] [<CommonParameters>]

DatabaseObject

Restore-SqlDscDatabase -DatabaseObject <Database> -BackupFile <String> [-RestoreType <String>] [-NoRecovery]
 [-Standby <String>] [-ReplaceDatabase] [-RelocateFile <RelocateFile[]>] [-Checksum] [-RestrictedUser]
 [-KeepReplication] [-FileNumber <Int32>] [-ToPointInTime <DateTime>] [-StopAtMarkName <String>]
 [-StopAtMarkAfterDate <DateTime>] [-StopBeforeMarkName <String>] [-StopBeforeMarkAfterDate <DateTime>]
 [-BlockSize <Int32>] [-BufferCount <Int32>] [-MaxTransferSize <Int32>] [-Force] [-PassThru]
 [-WhatIf] [-Confirm] [<CommonParameters>]

DESCRIPTION

This command restores a SQL Server database from a backup file using SQL Server Management Objects (SMO). It supports full, differential, transaction log, and file restores with options for file relocation, point-in-time recovery, and various recovery states.

EXAMPLES

EXAMPLE 1

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase.bak'

Performs a full restore of the database named MyDatabase from the specified backup file.

EXAMPLE 2

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase.bak' -ReplaceDatabase -Force

Performs a full restore of the database named MyDatabase, replacing the existing database without prompting for confirmation.

EXAMPLE 3

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase.bak' -NoRecovery
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase_Diff.bak' -RestoreType 'Differential' -NoRecovery
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase.trn' -RestoreType 'Log'

Performs a restore sequence: full backup with NORECOVERY, differential backup with NORECOVERY, and finally a transaction log backup with RECOVERY.

EXAMPLE 4

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$relocateFiles = @(
    [Microsoft.SqlServer.Management.Smo.RelocateFile]::new('MyDatabase', 'D:\SQLData\MyDatabase.mdf')
    [Microsoft.SqlServer.Management.Smo.RelocateFile]::new('MyDatabase_log', 'L:\SQLLogs\MyDatabase_log.ldf')
)
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase.bak' -RelocateFile $relocateFiles

Performs a full restore with specific file relocations using RelocateFile objects.

EXAMPLE 5

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'MyInstance'
$serverObject | Restore-SqlDscDatabase -Name 'MyDatabase' -BackupFile 'C:\Backups\MyDatabase.trn' -RestoreType 'Log' -ToPointInTime '2024-01-15T14:30:00'

Performs a point-in-time restore of a transaction log backup.

PARAMETERS

-BackupFile

Specifies the full path to the backup file. For full and differential restores, use the .bak extension. For transaction log restores, use the .trn extension.

Type: String
Parameter Sets: (All)
Aliases:

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

-BlockSize

Specifies the physical block size in bytes. Supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768, and 65536 bytes.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-BufferCount

Specifies the number of I/O buffers to use for the restore operation.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-Checksum

Specifies that checksums should be verified during the restore operation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-DatabaseObject

Specifies a database object to restore to. This is used when restoring differential or log backups to an existing database.

Type: Database
Parameter Sets: DatabaseObject
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-FileNumber

Specifies the backup set number to restore when the backup file contains multiple backup sets.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-Force

Specifies that the restore should be performed without any confirmation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-KeepReplication

Specifies that replication settings should be preserved during restore.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-MaxTransferSize

Specifies the maximum transfer size in bytes between SQL Server and the backup media.

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 0
Accept pipeline input: False
Accept wildcard characters: False

-Name

Specifies the name of the database to restore to.

Type: String
Parameter Sets: ServerObject
Aliases:

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

-NoRecovery

Specifies that the database should be left in a restoring state after the restore operation. This allows additional differential or log backups to be restored. Cannot be used together with Standby.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-PassThru

Specifies that the restored database object should be returned.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-Refresh

Specifies that the ServerObject's databases should be refreshed before accessing the database. This is helpful when databases could have been modified outside of the ServerObject, for example through T-SQL.

Type: SwitchParameter
Parameter Sets: ServerObject
Aliases:

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

-RelocateFile

Specifies an array of RelocateFile objects that define how to relocate database files during restore. Each RelocateFile object contains a LogicalFileName and PhysicalFileName property.

Type: RelocateFile[]
Parameter Sets: (All)
Aliases:

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

-ReplaceDatabase

Specifies that the existing database should be replaced. This is equivalent to the WITH REPLACE option in T-SQL.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-RestoreType

Specifies the type of restore to perform. Valid values are 'Full', 'Differential', 'Log', and 'Files'. Default value is 'Full'.

Type: String
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: Full
Accept pipeline input: False
Accept wildcard characters: False

-RestrictedUser

Specifies that access to the restored database should be restricted to members of the db_owner, dbcreator, or sysadmin roles.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

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

-ServerObject

Specifies the current server connection object.

Type: Server
Parameter Sets: ServerObject
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-Standby

Specifies the path to the standby (undo) file. When specified, the database is left in standby mode, which allows read-only access while additional log backups can still be applied. Cannot be used together with NoRecovery.

Type: String
Parameter Sets: (All)
Aliases:

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

-StopAtMarkAfterDate

Specifies the date/time after which to look for the mark specified by StopAtMarkName. If not specified, the restore stops at the first mark with the specified name.

Type: DateTime
Parameter Sets: (All)
Aliases:

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

-StopAtMarkName

Specifies the name of a marked transaction to stop at during restore. The restore includes the marked transaction.

Type: String
Parameter Sets: (All)
Aliases:

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

-StopBeforeMarkAfterDate

Specifies the date/time after which to look for the mark specified by StopBeforeMarkName. If not specified, the restore stops before the first mark with the specified name.

Type: DateTime
Parameter Sets: (All)
Aliases:

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

-StopBeforeMarkName

Specifies the name of a marked transaction to stop before during restore. The restore does not include the marked transaction.

Type: String
Parameter Sets: (All)
Aliases:

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

-ToPointInTime

Specifies a point in time to restore the database to. This parameter is only valid for transaction log restores when the database uses the Full or Bulk-Logged recovery model.

Type: DateTime
Parameter Sets: (All)
Aliases:

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

-Confirm

Prompts you for confirmation before running the cmdlet.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf

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

-WhatIf

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

Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi

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

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

INPUTS

Microsoft.SqlServer.Management.Smo.Server

Server object accepted from the pipeline (ServerObject parameter sets).

Microsoft.SqlServer.Management.Smo.Database

Database object accepted from the pipeline (DatabaseObject parameter sets).

OUTPUTS

None. By default, this command returns no output.

Microsoft.SqlServer.Management.Smo.Database

When PassThru is specified, returns the restored database object.

NOTES

RELATED LINKS