# Run SELECT @@servername in SQL Server Management Studio to get the value for $targetSqlserver

Import-Module sqlserver



# User defined global Variables for all databases
# BEGIN - USER INPUT
$targetSqlserver = "XXX\XXX"
$databaseDataPath = "G:\SQL Server\Data"
$databaseLogPath = "G:\SQL Server\Log"
$dbUsername = "User_Website_Login"
$dbPassword = "Password"
# END - USER INPUT

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($targetSqlserver)
function CreateLoginUser() {
    # Check if login user exists. If not, then create the login user.
    $log = $server.Logins["$dbUsername"]
    if (!$log){
        $NewLogin = New-Object Microsoft.SqlServer.Management.Smo.Login($server, $dbUsername)
        $NewLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
        $NewLogin.Create($dbPassword)
    }

}

#function RestoreDatabase ($databaseName, $backupFile, $logicalDataName, $logicalLogName) {
function RestoreDatabase ($databaseName, $backupFile) {


    # $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logicalDataName, "$databaseDataPath\$databaseName.mdf")
    # $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($logicalLogName, "$databaseLogPath\$databaseName.ldf")

    $relocateFiles = @()

    $rs = new-object('Microsoft.SqlServer.Management.Smo.Restore')
    $bdi = new-object ('Microsoft.SqlServer.Management.Smo.BackupDeviceItem') ($backupFile, 'File')
    $rs.Devices.Add($bdi)
    $fl = $rs.ReadFileList($server)
    #$fl | ForEach-Object {echo $_.LogicalName  $_.Type }
    $fl | ForEach-Object {
        $extension = [System.IO.Path]::GetExtension($_.PhysicalName)
        $fileLo =  $databaseLogPath+"\"+$databaseName+"_"+$_.LogicalName+$extension
        $relocateFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($_.LogicalName, $fileLo)
        $relocateFiles += $relocateFile
    }



    # If the database we wish to restore already exists we need to drop it first.
    if(![string]::IsNullOrEmpty($server.Databases["$databaseName"]))
    {
        echo "Existing database '$databaseName' found. Dropping database!"
        Invoke-SqlCmd -ServerInstance $targetSqlserver "USE [master]; ALTER DATABASE [$databaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$databaseName]"
    }

    # Restore database.
    echo "Restoring database '$databaseName'."
    Restore-SqlDatabase -ServerInstance $targetSqlserver -Database $databaseName -BackupFile $backupFile -RelocateFile $relocateFiles

    # # When restoring a database from an other server the database users are usually included.
    # # Because these users does not exists on the target server as login users we need to delete them.
    $database = $server.Databases["$databaseName"]
    $users = $database.Users;
    $test = @()
    foreach($user in $users)
    {
        if($user.Name.Contains($dbUsername))
        {
            $test += $user.Name
        }
    }

    foreach($user in $test)
    {
        echo "Deleting existing database user!"
        echo $user
        $database.Users[$user].Drop();
    }

    # Create a new database user for the login user
    echo "Adding login user '$dbUsername' as db_owner."
    $NewUser = New-Object Microsoft.SqlServer.Management.Smo.User($database, $dbUsername)
    $NewUser.Login = $dbUsername
    $NewUser.Create()
    $NewUser.AddToRole("db_owner")

}

CreateLoginUser