SQL database Restore using Powershell set Variable

Question

Situation:

We have a hosted application that we need direct access to the SQL server but not able to. The work around is the Host provides a weekly backup via SFTP.

I have a Script that downloads the backup using WinSCP:

CMD Batch Script ran to run the WinSCP Script (saved and ran in WinSCP directory)

WinSCP.com /script=sftpscript.txt

The WinSCP Script ran:

 open sftp://<<Serveraddress and login>><<REMOTE SFTP DIRECTORY>>
 synchronize local -delete "<<LOCAL DIRECTORY" "<<REMOTE SFTP DIRECTORY>>"
 exit

The backup downloaded has a timestamp at the end of the file name: BACKUP_20170526_222744.BAK

I am needing to Query the Update filename automatically and use that filename as a variable to restore the Backup. I have figured out how to pull the filename and set as a variable in Powershell here:

set-location -path '<<LOCAL BACKUP DIRECTORY>>'
$bak = ls | where-object {$_.Name -Like '*.BAK'} | select-object -expandproperty name

To Restore the Database in TSQL I am using the following Script currently but with manually enter backup filename:

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 the Powershell and TSQL scripts do as intended currently. The Powershell sets the $bak variable as the correct Filename for the backup The SQL Script will restore the backup but only if manually enter the file name of the backup.

My issue is getting that Powershell Variable $bak to be used in my TSQL script.

Please note I am at a entry level when it comes to both Powershell and TSQL.

Here is the Commanded 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

Permission Script

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 put the backup into the directory.

Further curious then why your company does not consider a dba to manage restores. Does your system have just simple recovery? If it uses full and/or differential backups you must account for the order of sequential backups from the time of the last full backup. — clifton_h
Are you aware of what single_user does? The docs have gotten a fresh facelift and are much easier to learn. Even if someone gave you a script, you will be responsible for this change. Hence ask a dba — clifton_h
It appears that the TSQL script can use shell environment variables. See section C of docs.microsoft.com/en-us/sql/relational-databases/scripting/… — lit
This restore is not for backup purposes. This restored SQL database is being used for Tableau. The single users is for all of the Tableau workbooks to be able to have access. This is not used for any other purpose. — Cody

Unknown Unknown · Accepted Answer · 2017-05-26T21:53:59

You can utilize a community-based module called dbatools. The command you would want is Restore-DbaDatabase, full code can be found here.

If you only keep that one backup file each time to restore, you can simply call the restore function and point it at 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 down to C:\MSSQL\Backups, where your SQL Server instance has access to that directory as well. 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 review the help of the command to see the other parameter options. One I use frequently is add -OutputScriptOnly, this will generate the T-SQL script for you if you want to run it in a different process/method.

SQL database Restore using Powershell set Variable

1 Answers