CODEWITHSUNDEEP
sql-serverpowershelldbatools
Cody
Cody

Reputation: 23

SQL database Restore using Powershell set Variable

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.

Upvotes: 2

Views: 1853

Answers (1)

user847990
user847990

Reputation:

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.

Upvotes: 1

Related Questions