Restore-DbaDatabase,dbatools

We can use below dbatools to restore database. Export-DbaUser will export all the respective database users to a spcific file. If you want all the user script need to add single file then user -FilePath parameter.If you want indivual files need to created user -Path parameter. 
cls
Export-DbaUser `
-SqlInstance InstanceNameHere `
-Database DatabaseNameHere `
-FilePath C:\Temp\Users_DatbaseNameHere_InstanceNameHere.sql `
-Verbose

In the below command -Path parameter you no need pass .BAK file it will automatically read file. -WithReplace option will overwrite the existing database.
Restore-DbaDatabase `
-SqlInstance InstanceNameHere `
-DatabaseName DatabaseNameHere `
-Path H:\TodayRestore `
-DestinationDataDirectory "F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatabaseName_Data.mdf" `
-DestinationLogDirectory "G:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\DatbaseName_Log.ldf" `
-WithReplace -Verbose

Get-DbaDbOrphanUser `
-SqlInstance InstanceNameHere `
-Database DatabaseNameHere `
-Verbose|Format-Table -AutoSize 

Repair-DbaDbOrphanUser `
-SqlInstance InstanceNameHere `
-Database DatabaseName `
-Verbose
Providing the image like below.


Also see the result below, this WILL NOT RESTORE the database, instead it provide script. Because we have mentioned -OutputScriptOnly parameter.



 
<#
1) This code generate script for restoration not execute directly. Because I mentioned -OutputScriptOnly parameter
2) This will use destination default data and log paths directly
3) In the -Path parameter whatever value you provide ensure that you have should have only .BAK OR .trn failes available
4) -GetBackupinformation will catpure teh .bak files infromation from which file you have restored the database.While you running the command run command
like $backupinfo
#>
$result = Restore-DbaDatabase `
 -SqlInstance RAMESH\UAT `
 -Path "G:\BACKUPS_RAMESH\AdventureWorks2014" `
 -OutputScriptOnly `
 -UseDestinationDefaultDirectories `
 -ExecuteAs sa `
 -GetBackupInformation backupinfo `
 -Verbose
 $result | Out-File -FilePath "G:\BACKUPS_RAMESH\AdventureWorks2014\Restore.sql"
Getting right backup files to be restored.
cls
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size(300, 5000)
$DBs = 'dbname'
$DBs | foreach {
$DB = $_
''
"/*========= Database Name : $($DB) ============*/"
''
$path = -join('\\a\\SQL-Backups\\b\\c\\', $DB, '\\*.*')
$FullName = (Get-ChildItem -Recurse -Path $path | ?{ $_.Name -match '.' -and $_.LastWriteTime -gt '11/20/2025 5:04 AM' }).FullName
foreach($file in $FullName) {
IF($file -match '.bak') {
Write-Output "EXECUTE AS LOGIN ='SA'"
Write-Output ""
Write-Output @"
restore database [$($DB)]`r`nfrom disk ='$($file)'
with Norecovery, stats =2
"@
} else {
Write-Output @"
restore log [$($DB)] `r`nfrom disk ='$($file)'
with Norecovery, stats =2
"@
}
}
Write-Output ""
Write-Output @"
ALTER DATABASE [$($DB)]
SET HADR AVAILABILITY GROUP = [aggroupnamehere];
"@
}
What is the last backup that is already restored 

SELECT
    r.destination_database_name,
    r.restore_date,
    r.backup_set_id,
    b.type AS backup_type,
    b.first_lsn,
    b.last_lsn,
    b.checkpoint_lsn,
    b.database_backup_lsn,
    mf.physical_device_name AS backup_file_path,
    *
FROM msdb.dbo.restorehistory r
JOIN msdb.dbo.backupset b 
    ON r.backup_set_id = b.backup_set_id
JOIN msdb.dbo.backupmediafamily mf
    ON b.media_set_id = mf.media_set_id
WHERE r.destination_database_name = 'dbname'
ORDER BY r.restore_date DESC;

Comments

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'...
Read more

The transaction log for database is full due to 'OLDEST_PAGE'

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.
Read more

SSAS Cube or Database backup with Powershell command "Backup-AsDatabase"

Image
Run the full code from Load assembly section. # Load the assembly #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null # Create the server object $server = New-Object Microsoft.AnalysisServices.Server $server . Connect ( "localhost" ) # Or your SSAS instance name #Date Converion with minutes and seconds $date = ( get-date ).ToString( "MM_dd_yyyy_hh_mm_ss" ) $Server = New-Object Microsoft.AnalysisServices.Server $Server .Connect( "MAPS\TEST" ) $dbservers = $Server .Databases| Select-Object Name,ParentServer $dbservers | ForEach -Object{ $inddb = $_ #The "Backup-AsDatabase" is especially for SSAS cube backup and downloaded from "SQLServer" powershell module Backup-ASDatabase -BackupFile "E:\MAPS_TEST\SSAS_BACKUP\NEW\ $( $ inddb.Name) _ $( $ date) .abf" -Name $inddb .Name -Verbose -Server $inddb .ParentServer Write-Host "SSAS cube backup [ $( $ i...
Read more