SQL Database Recovery Using Powershell Set Variable
Situation:
We have a hosted application that requires direct access to a SQL server but is unable to. Work around the host provides weekly backups via SFTP.
I have a Script that downloads a backup using WinSCP:
CMD Script package was run to run WinSCP Script (saved and run in WinSCP directory)
WinSCP.com /script=sftpscript.txt
WinSCP Script is running:
open sftp://<<Serveraddress and login>><<REMOTE SFTP DIRECTORY>>
synchronize local -delete "<<LOCAL DIRECTORY" "<<REMOTE SFTP DIRECTORY>>"
exit
The downloaded backup has a time stamp at the end of the file name: BACKUP_20170526_222744.BAK
I need to prompt for the update file name automatically and use that file name as a variable to restore the backup. I figured out how to pull the filename and set a variable here in Powershell:
set-location -path '<<LOCAL BACKUP DIRECTORY>>'
$bak = ls | where-object {$_.Name -Like '*.BAK'} | select-object -expandproperty name
To restore a database in TSQL, I am using the following Script currently, but with manually entering the name of the backup file:
USE [master]
ALTER DATABASE [DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [DB] FROM DISK = N'<<LOCAL DIRECTORY>>\<<BACKUP FILE>>' WITH FILE = 1, MOVE N'DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DB.mdf', MOVE N'DB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DB_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO
ALTER DATABASE [DB] SET MULTI_USER
GO
USE [DB]
GO
CREATE USER [Reader] FOR LOGIN [Reader]
GO
USE [DB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Reader]
GO
Both Powershell and TSQL scripts work differently. Powershell sets the $ bak variable as the correct file name for the backup. SQL Script will restore the backup, but only if you manually enter the backup file name.
My problem is Powershell Variable $ bak will be used in my TSQL script.
Please note: I am at the beginner level when it comes to PowerShell and TSQL.
Here's Commanded being used to complete this based on Shawn Melton's answer below:
Restore-DBADatabase -SqlInstance localhost -path 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup' -WithReplace -UseDestinationDefaultDirectories
invoke-sqlcmd -inputfile "Permission.sql" -serverinstance localhost
Script resolution
USE [DB]
GO
CREATE USER [Reader] FOR LOGIN [Reader]
GO
USE [DB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Reader]
GO
This command is used after the WinSCP command has placed the backup in the directory.
source to share
You can use a community module called dbatools . The command you want is Restore-DbaDatabase
, the complete code can be found here .
If you only save one backup file each time to restore, you can simply call the restore function and point it in the directory. It will pick up the backup and then restore it since it is just a full backup.
So, as an example, if you pull the backup file to C:\MSSQL\Backups
where your instance of SQL Server also has access to that directory. You can run this code:
Import-Module dbatools
Restore-DbaDatabase -SqlServer SQLInstanceName -Path 'C:\MSSQL\Backups' -WithReplace `
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\' `
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\'
You can view the command help to see other options. I use add a lot -OutputScriptOnly
, this will generate a T-SQL script for you in case you want to run it in another process / method.
source to share