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.
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.
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
<# 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"
Comments