Create an SQL Backup without administrator priveleges

Question

I have a script that makes a backup of 3 sql databases.

I've attached the full script and its output in the two code blocks below. Don't be intimidated by their size, i'm simply calling the powershell cmdlet Backup-SqlDatabase at some point.

Backup-SqlDatabase documentation

https://learn.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps

sqlServerInstanceName = 'localhost\SQLEXPRESS2014' 
$sqlUserName = 'SomeName'
$sqlPassword = 'SomePassword'
$secureString = ConvertTo-SecureString $sqlPassword -AsPlainText -Force

...

    # Backing up Sql database
    #

    # SQL cmdlets below need some dll imports from system
    # These do not get loaded sometime when running under an non-admin account (Cannof find a provider with the name 'SqlServer')
    # Running this dummy command seems to load all needed dlls
    # Also see: https://www.sqlservercentral.com/forums/topic/unable-access-sql-provider-in-powershell-without-running-an-invoke-sqlcmd-first
    Invoke-Sqlcmd | Out-Null

    # To use any SQL cmdlets we need to create a ps virtual drive with the right authenitcation
    # See: https://learn.microsoft.com/en-us/sql/powershell/manage-authentication-in-database-engine-powershell?view=sql-server-ver15#sql-server-authentication-using-a-virtual-drive
    # NOTE the above docs are a bit confusing, this seemed to work: https://social.technet.microsoft.com/Forums/en-US/f9901f20-01db-4d6e-bcfd-ecb5ca3ed64c/powershell-sqlserver-connect-via-newpsdrive?forum=winserverpowershell

    $sqlPsDrivename = 'sqlPsDrive'
    $sqlRoot = "SQLSERVER:\SQL\$sqlServerInstanceName"
    $sqlCred = new-object System.Management.Automation.PSCredential -ArgumentList $sqlUserName,$secureString
    
    if (Test-Path "$($sqlPsDrivename):")
    {
        Remove-PSDrive $sqlPsDrivename -PSProvider SqlServer -Scope 1 
    }
    
    $null = New-PSDrive $sqlPsDrivename -PSProvider SqlServer -Root $sqlRoot -Credential $sqlCred -Scope 1  
    
    $null = Set-Location "$($sqlPsDrivename):"

    [Microsoft.SqlServer.Management.Smo.Database[]] $allDatabases = Get-SqlDatabase -ServerInstance $sqlServerInstanceName 
    $databases = $allDatabases | Where-Object {$_.IsSystemObject -eq $false}

    if($databases -eq $null)
    {
        throw "No non-system databases were found in $sqlServerInstanceName"
    }

    $null = [System.IO.Directory]::CreateDirectory($sqlBackupDirectory)
    Write-Host Going to backup $databases.Count databases to $sqlBackupDirectory
    
    $databases | Backup-SqlDatabase -BackupContainer $sqlBackupDirectory -Verbose

    Write-Host Done with backing up $databases.Count databases to $sqlBackupDirectory  

This seems to work as it should, however it only works when I run this under windows account Administrator. However when I run this under a regular user account e.g. Operator this fails with the following script output:

Going to backup 3 databases to C:\ITM\FullBackup\Temp\Database
VERBOSE: Performing the operation "Backup-SqlDatabase" on target "[localhost\SQLEXPRESS2014]".
VERBOSE: 
        declare @HkeyLocal nvarchar(18)
        declare @ServicesRegPath nvarchar(34)
        declare @SqlServiceRegPath sysname
        declare @BrowserServiceRegPath sysname
        declare @MSSqlServerRegPath nvarchar(31)
        declare @InstanceNamesRegPath nvarchar(59)
        declare @InstanceRegPath sysname
        declare @SetupRegPath sysname
        declare @NpRegPath sysname
        declare @TcpRegPath sysname
        declare @RegPathParams sysname
        declare @FilestreamRegPath sysname

        select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

        -- Instance-based paths
        select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
        select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
        select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
        select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
        select @RegPathParams=@InstanceRegPath+'\Parameters'

        -- Services
        select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
        select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
        select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'

        -- InstanceId setting
        select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

        -- Network settings
        select @NpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Np'
        select @TcpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Tcp'
      


        declare @SmoAuditLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT
      


        declare @NumErrorLogs int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT
      


        declare @SmoLoginMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'LoginMode', @SmoLoginMode OUTPUT
      


        declare @SmoMailProfile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'MailAccountName', @SmoMailProfile OUTPUT
      


        declare @BackupDirectory nvarchar(512)
        if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0)
          select @BackupDirectory=cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512))
        else
          exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT
      


        declare @SmoPerfMonMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'Performance', @SmoPerfMonMode OUTPUT

        if @SmoPerfMonMode is null
        begin
        set @SmoPerfMonMode = 1000
        end
      


        declare @InstallSqlDataDir nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT
      


        declare @MasterPath nvarchar(512)
        declare @LogPath nvarchar(512)
        declare @ErrorLog nvarchar(512)
        declare @ErrorLogPath nvarchar(512)

        select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) from master.sys.database_files where name=N'master'
        select @LogPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
        select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
        select @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex('\', reverse(@ErrorLog)))
      


        declare @SmoRoot nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @SmoRoot OUTPUT
      


        declare @ServiceStartMode int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'Start', @ServiceStartMode OUTPUT
      


        declare @ServiceAccount nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT
      


        declare @NamedPipesEnabled int
        exec master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT
      


        declare @TcpEnabled int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT
      


        declare @InstallSharedDirectory nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @InstallSharedDirectory OUTPUT
      


        declare @SqlGroup nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLGroup', @SqlGroup OUTPUT
      


        declare @FilestreamLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'EnableLevel', @FilestreamLevel OUTPUT
      


        declare @FilestreamShareName nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'ShareName', @FilestreamShareName OUTPUT
      


        declare @cluster_name nvarchar(128)
        declare @quorum_type tinyint
        declare @quorum_state tinyint
        BEGIN TRY
            SELECT @cluster_name = cluster_name, 
                @quorum_type = quorum_type,
                @quorum_state = quorum_state
            FROM sys.dm_hadr_cluster
        END TRY
        BEGIN CATCH
            IF(ERROR_NUMBER() NOT IN (297,300))
            BEGIN
                THROW
            END
        END CATCH
      

SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode],
ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory],
CAST(@@SERVICENAME AS sysname) AS [ServiceName],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile],
SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
@ServiceStartMode AS [ServiceStartMode],
ISNULL(@ServiceAccount,N'') AS [ServiceAccount],
CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled],
CAST(@TcpEnabled AS bit) AS [TcpEnabled],
ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory],
ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup],
case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 
0 end AS [PolicyHealthState],
@FilestreamLevel AS [FilestreamLevel],
ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName],
-1 AS [TapeLoadWaitTime],
CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled],
SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus],
ISNULL(@cluster_name, '') AS [ClusterName],
ISNULL(@quorum_type, 4) AS [ClusterQuorumType],
ISNULL(@quorum_state, 3) AS [ClusterQuorumState],
SUSER_SID(@ServiceAccount, 0) AS [ServiceAccountSid],
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Name],
CAST(
        ISNULL(serverproperty(N'instancename'),N'')
       AS sysname) AS [InstanceName],
CAST(0x0001 AS int) AS [Status],
0 AS [IsContainedAuthentication],
CAST(null AS int) AS [ServerType]
VERBOSE: RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
VERBOSE: RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
VERBOSE: 
        declare @HkeyLocal nvarchar(18)
        declare @ServicesRegPath nvarchar(34)
        declare @SqlServiceRegPath sysname
        declare @BrowserServiceRegPath sysname
        declare @MSSqlServerRegPath nvarchar(31)
        declare @InstanceNamesRegPath nvarchar(59)
        declare @InstanceRegPath sysname
        declare @SetupRegPath sysname
        declare @NpRegPath sysname
        declare @TcpRegPath sysname
        declare @RegPathParams sysname
        declare @FilestreamRegPath sysname

        select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

        -- Instance-based paths
        select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
        select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
        select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
        select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
        select @RegPathParams=@InstanceRegPath+'\Parameters'

        -- Services
        select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
        select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
        select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'

        -- InstanceId setting
        select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

        -- Network settings
        select @NpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Np'
        select @TcpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Tcp'
      


        declare @ServiceInstanceId nvarchar(512)
        EXEC master.sys.xp_regread @HkeyLocal, @InstanceNamesRegPath, @@SERVICENAME, @ServiceInstanceId OUTPUT
      

SELECT
ISNULL(@ServiceInstanceId,N'') AS [ServiceInstanceId]
D:\Projects\Hmi.Ushape\DistributedFiles\FullBackup\FullBackup.ps1 : An exception occurred while executing a Transact-SQL statement or batch.
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,FullBackup.ps1
 
Script end.

In both cases, I connect with a SQL server account - not a windows account. This account has DBO rights for the 3 databases - so in my mind it should be able to always create a backup.

In the verbose logging of the Backup-SqlDatabase command you can see: VERBOSE: RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'. I also see that apparently it tries to read from HKLM which of course is only accessible under an administrator account, and maybe even elevation.

Is there another way for me to create a backup (or copy the .mdf/.ldf files, without sql locking the files) where I don't have to run under an administrator account? I only have an sql account with ownership to those 3 databases, and a user windows account.

Answer 1

To complete the answer, I dropped usage of Backup-SqlDatabase and i edited my script as follows:

BackupDatabase.sql

BACKUP DATABASE [$(dbName)] TO DISK = N'$(filePath)' WITH 
    FORMAT, INIT, NAME = N'$(dbName)-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD, CHECKSUM

FullBackup.ps1

function Backup-SingleDatabase
{
    Param(

        [string] $dbName,
        [string] $filePath

    )
        
    Write-Host Backing up $dbName to $filePath

    $SqlcmdVariables= @(
        "dbName=$dbName",
        "filePath=$filePath"
    )

    Invoke-Sqlcmd -InputFile $BackupDatabaseSqlFilePath -Database $dbName -Variable $SqlcmdVariables -OutputSqlErrors $true -SuppressProviderContextWarning
}

...

# Backing up Sql database
#

# SQL cmdlets below need some dll imports from system
# These do not get loaded sometime when running under an non-admin account (Cannof find a provider with the name 'SqlServer')
# Running this dummy command seems to load all needed dlls
# Also see: https://www.sqlservercentral.com/forums/topic/unable-access-sql-provider-in-powershell-without-running-an-invoke-sqlcmd-first
Invoke-Sqlcmd | Out-Null

# To use any SQL cmdlets we need to create a ps virtual drive with the right authenitcation
# See: https://learn.microsoft.com/en-us/sql/powershell/manage-authentication-in-database-engine-powershell?view=sql-server-ver15#sql-server-authentication-using-a-virtual-drive
# NOTE the above docs are a bit confusing, this seemed to work: https://social.technet.microsoft.com/Forums/en-US/f9901f20-01db-4d6e-bcfd-ecb5ca3ed64c/powershell-sqlserver-connect-via-newpsdrive?forum=winserverpowershell

$sqlPsDrivename = 'sqlPsDrive'
$sqlRoot = "SQLSERVER:\SQL\$sqlServerInstanceName"
$secureString = ConvertTo-SecureString $sqlPassword -AsPlainText -Force
$sqlCred = new-object System.Management.Automation.PSCredential -ArgumentList $sqlUserName,$secureString

if (Test-Path "$($sqlPsDrivename):")
{
    Remove-PSDrive $sqlPsDrivename -PSProvider SqlServer -Scope 1 
}

$null = New-PSDrive $sqlPsDrivename -PSProvider SqlServer -Root $sqlRoot -Credential $sqlCred -Scope 1  

$null = Set-Location "$($sqlPsDrivename):"

[Microsoft.SqlServer.Management.Smo.Database[]] $allDatabases = Get-SqlDatabase -ServerInstance $sqlServerInstanceName 
$databases_ = $allDatabases | Where-Object {$_.IsSystemObject -eq $false}

if($databases_ -eq $null)
{
    throw "No non-system databases were found in $sqlServerInstanceName"
}

[Microsoft.SqlServer.Management.Smo.Database[]] $databases = $databases_

$null = [System.IO.Directory]::CreateDirectory($sqlBackupDirectory)
Write-Host Going to backup $databases.Count databases to $sqlBackupDirectory

$databases | % {Backup-SingleDatabase -dbName "$($_.Name)" -filePath "$sqlBackupDirectory\$($_.Name)_$($hostName)_$(get-date -f yyyyMMdd-HHmmss).bak"}

# Cleaning up after we're done
Pop-Location

The following article helped with passing arguments to the sql script: https://www.dbbest.com/blog/using-powershell-invoke-sqlcmd-with-variable/

If I were to redo this, I would probably put all logic in the .sql file and call that instead. Now I have some logic in the .ps1 file regarding fetching all user databases in the server, and then I call my .sql script for each database. I can't be bothered with rewriting that at this point though.

Answer 2

My answer would not explain how to use the Backup-SqlDatabase in PowerShell, nor how to configure permissions needed to run this cmdlet.

When I wanted to backup a database with a PowerShell script I did not use the Backup-SqlDatabase cmdlet. I prefer to compose a native SQL BACKUP DATABASE command.

So, I created two files. One was a PowerShell script, another was a SQL script.

make_backup.sql has a BACKUP DATABASE command:

BACKUP DATABASE [DBName] TO DISK = N'D:\BACKUP\DBNameFull.bak' WITH 
    FORMAT, INIT, NAME = N'DBName-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION, CHECKSUM

Adjust the options as needed.

make_backup.ps1 executes the SQL script using sqlcmd:

& sqlcmd -S DBName -U UserName -P UserPassword -i "make_backup.sql"