Author Archives: cviorel - Page 2

Register-PSRepository fails on Powershell Core when running behind a corporate proxy

Posted by cviorel on April 17, 2020 2 comments | This post has 35,735 views.

Trying to register a psrepository behind a corporate proxy fails on Powershell Core.

$credential = Get-Credential
$uri = 'https://localhost:8080/artifactory/api/nuget/custom_repository'
$repoParams = @{
    Name                      = 'custom_repository'
    SourceLocation            = $uri
    PublishLocation           = $uri
    InstallationPolicy        = 'Trusted'
    PackageManagementProvider = 'NuGet'
    Verbose                   = $true
}
Register-PSRepository @repoParams -Credential $credential

$credential = Get-Credential

$uri = 'https://localhost:8080/artifactory/api/nuget/custom_repository'

$repoParams = @{

Name = 'custom_repository'

SourceLocation = $uri

PublishLocation = $uri

InstallationPolicy = 'Trusted'

PackageManagementProvider = 'NuGet'

Verbose = $true

}

The error you receive is:

Register-PSRepository: The specified Uri 'https://localhost:8080/artifactory/api/nuget/custom_repository' for parameter 'SourceLocation' is an invalid Web Uri. Please ensure that it meets the Web Uri requirements.

1	Register-PSRepository: The specified Uri 'https://localhost:8080/artifactory/api/nuget/custom_repository' for parameter 'SourceLocation' is an invalid Web Uri. Please ensure that it meets the Web Uri requirements.

Running the same on Windows Powershell 5.1 works.
Now, let see why it’s not working on Powershell Core.

Trying to find more details about the error message:

$Error[0] | Select-Object *

1	$Error[0] \| Select-Object *

PSMessageDetails      :
Exception             : System.ArgumentException: The specified Uri 'https://localhost:8080/artifactory/api/nuget/custom_repository' for parameter 'SourceLocation' is an invalid Web Uri. Please ensure
                        that it meets the Web Uri requirements.
                           at System.Management.Automation.MshCommandRuntime.ThrowTerminatingError(ErrorRecord errorRecord)
TargetObject          : https://localhost:8080/artifactory/api/nuget/custom_repository
CategoryInfo          : InvalidArgument: (https://localhost…l-custom_repository:String) [Register-PSRepository], ArgumentException
FullyQualifiedErrorId : InvalidWebUri,Register-PSRepository
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at ThrowError, C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1: line 7113
                        at Resolve-Location, C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1: line 6121
                        at Register-PSRepository, C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1: line 11423
                        at , : line 1
PipelineIterationInfo : {}

PSMessageDetails :

Exception : System.ArgumentException: The specified Uri 'https://localhost:8080/artifactory/api/nuget/custom_repository' for parameter 'SourceLocation' is an invalid Web Uri. Please ensure

that it meets the Web Uri requirements.

at System.Management.Automation.MshCommandRuntime.ThrowTerminatingError(ErrorRecord errorRecord)

TargetObject : https://localhost:8080/artifactory/api/nuget/custom_repository

CategoryInfo : InvalidArgument: (https://localhost…l-custom_repository:String) [Register-PSRepository], ArgumentException

FullyQualifiedErrorId : InvalidWebUri,Register-PSRepository

ErrorDetails :

InvocationInfo : System.Management.Automation.InvocationInfo

ScriptStackTrace : at ThrowError, C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1: line 7113

at Resolve-Location, C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1: line 6121

at Register-PSRepository, C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1: line 11423

at , : line 1

PipelineIterationInfo : {}

Let’s look at the function inside the module where the error is triggered (bonus tip on how to jump directly to the line number)

code -g 'C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1:6121'

1	code -g 'C:\program files\powershell\7\Modules\PowerShellGet\PSModule.psm1:6121'

The error message is misleading, as it turns out the part that is failing is this one:

$pingResult = Ping-Endpoint -Endpoint $Location -Credential $Credential -Proxy $Proxy -ProxyCredential $ProxyCredential

1	$pingResult = Ping-Endpoint -Endpoint $Location -Credential $Credential -Proxy $Proxy -ProxyCredential $ProxyCredential

We know the endpoint location is valid, so that means we cannot pass through the proxy.
Digging around the internet led me to this GitHub issue.

Apparently, .NET Core is not respecting proxy in some cases.

The recommended workaround from Steve Lee is to set

[System.AppContext]::SetSwitch("System.Net.Http.UseSocketsHttpHandler", $false)

1	[System.AppContext]::SetSwitch("System.Net.Http.UseSocketsHttpHandler", $false)

or to create

$env:DOTNET_SYSTEM_NET_HTTP_USESOCKETSHTTPHANDLER = 0

1	$env:DOTNET_SYSTEM_NET_HTTP_USESOCKETSHTTPHANDLER = 0

Turns out our little problem is now fixed and the Register-PSRepository command runs successfully.

I hope this post will save you some time in case you run into the same issue.

Voilà!

Block Chrome Software Reporter Tool

Posted by cviorel on April 16, 2020 No comments | This post has 29,588 views.

Use the following PowerShell Snippet to disable Chrome Software Reporter Tool from eating your CPU cycles:

Set-ItemProperty 'HKLM:\SOFTWARE\Policies\Google\Chrome' `
    -Name ChromeCleanupEnabled -Value 0
Set-ItemProperty 'HKLM:\SOFTWARE\Policies\Google\Chrome' `
    -Name ChromeCleanupReportingEnabled -Value 0

Set-ItemProperty 'HKLM:\SOFTWARE\Policies\Google\Chrome' `

-Name ChromeCleanupEnabled -Value 0

Set-ItemProperty 'HKLM:\SOFTWARE\Policies\Google\Chrome' `

-Name ChromeCleanupReportingEnabled -Value 0

ChromeCleanupEnabled’s value determines whether the Software Reporter Tool may run on the system.

ChromeCleanupReportingEnabled’s value determines whether the results are reported to Google.

Enable transmission-daemon logging to file

Posted by cviorel on October 26, 2018 3 comments | This post has 36,306 views.

If you use transmission-daemon on your Raspberry Pi and want to have logs to a file, you can follow a few simple steps to accomplish this:

Edit the service file:

sudo vim /lib/systemd/system/transmission-daemon.service

1	sudo vim /lib/systemd/system/transmission-daemon.service

Change the ExecStart line to look like this:

ExecStart=/usr/bin/transmission-daemon -f --log-debug --logfile /var/log/transmission.log

1	ExecStart=/usr/bin/transmission-daemon -f --log-debug --logfile /var/log/transmission.log

You can choose between these options:

--log-error
Show error messages

--log-info
Show error and info messages

--log-debug
Show error, info, and debug messages

--log-error

Show error messages

--log-info

Show error and info messages

--log-debug

Show error, info, and debug messages

systemctl will complain that the service file was changed. Run:

sudo systemctl daemon-reload

1	sudo systemctl daemon-reload

to reload units.

Create the log file and assign the correct ownership and permissions:

sudo touch /var/log/transmission.log
sudo chown debian-transmission /var/log/transmission.log
sudo chmod 644 /var/log/transmission.log

sudo touch /var/log/transmission.log

sudo chown debian-transmission /var/log/transmission.log

sudo chmod 644 /var/log/transmission.log

Restart the service:

sudo service transmission-daemon restart

1	sudo service transmission-daemon restart

Have fun!

dbatools – fully automated offsite backups, database restores and integrity checks

Posted by cviorel on November 14, 2017 No comments | This post has 754 views.

In this article you’ll find the process I implemented to test my backups using dbatools and PowerShell.

dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Backed up by a community of passionate people, it will do wonders in your environments.

Requirements:

Now let’s dive a bit into the tasks I wanted to tackle. I needed to:

copy my full backups to a separate location
run daily restores to measure how long it will take me to react in case of a disaster
perform daily integrity checks and still be able to run other maintenance tasks outside business hours (we have big databases > 1TB)
constantly measure how long it takes for each of the above steps to complete
not do all the above manually

Process:

To solve this, I designed the following process (simplified here to show only the interesting parts):

From the Production servers (Server A, Server B, Server C) I run usual full backups on a local drive. That’s done using Ola Hallengren’s MaintenanceSolution. From the C&C (Command and Control) server which has the dbatools module installed, we run remote PS sessions on the Production servers (Server A, Server B, Server C) which are checking for the last full backup using:

PowerShell

Get-DbaBackupHistory -SqlInstance $server -LastFull

1

Get-DbaBackupHistory -SqlInstance $server -LastFull

From the output I get the file name which is then copied to Server E (Samba share).
Once this is done, task 1 is solved.

The C&C server will run a PS remote session to Server D where it will restore the databases directly from the Samba share using:

Restore-DbaDatabase -SqlInstance $srv -Path $backupDirRoot\$dir\$dbName -MaintenanceSolutionBackup -WithReplace -DestinationFilePrefix $prefix -RestoredDatababaseNamePrefix $prefix -DestinationDataDirectory $dataDir -DestinationLogDirectory $logDir

1	Restore-DbaDatabase -SqlInstance $srv -Path $backupDirRoot\$dir\$dbName -MaintenanceSolutionBackup -WithReplace -DestinationFilePrefix $prefix -RestoredDatababaseNamePrefix $prefix -DestinationDataDirectory $dataDir -DestinationLogDirectory $logDir

With one line of code we are able to restore a bunch of databases, regardless if they were taken using different file paths or on different versions of SQL Server.
This nails the second task.

During the same session, right after the restores are finished, we trigger integrity checks:

$query = "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = '$dbName'"
Invoke-Sqlcmd2 -ServerInstance $targetServer -Database $dbName -Query "$query" -QueryTimeout 99999999

1 2	$query = "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = '$dbName'" Invoke-Sqlcmd2 -ServerInstance $targetServer -Database $dbName -Query "$query" -QueryTimeout 99999999

Task 3 done, two to go!

To measure the elapsed time of that particular command, I use:

Measure-Command

1	Measure-Command

and keep this in a custom PS object.

I’m writing the data from those custom PS objects to the a database located on the C&C Server.
Of course, dbatools hadles that for me:

# Record the results for File Copy
$DataTable = $result | Select-Object FNameSource, FNameDestination, FSizeMB, CopyDuration_hh:mm:ss.mss, PSComputerName, ExecutionDate | Out-DbaDataTable
Write-DbaDataTable -SqlInstance 'C&C Server' -InputObject $DataTable -Table autoDB.dbo.AutoFileCopy -AutoCreateTable

# Record the results for File Copy

$DataTable = $result | Select-Object FNameSource, FNameDestination, FSizeMB, CopyDuration_hh:mm:ss.mss, PSComputerName, ExecutionDate | Out-DbaDataTable

Write-DbaDataTable -SqlInstance 'C&C Server' -InputObject $DataTable -Table autoDB.dbo.AutoFileCopy -AutoCreateTable

# Record the results for restore db and integrity check operations
$DataTable = $RestoreCheckDBResult | Select-Object Database, RestoreDurationMinutes, RestoreExecutionDate, CheckDBDurationMinutes, LastGoodCheckDbStatus, LastGoodCheckDb | Out-DbaDataTable
Write-DbaDataTable -SqlInstance 'C&C Server' -InputObject $DataTable -Table autoDB.dbo.AutoRestoreCheckDB -AutoCreateTable

# Record the results for restore db and integrity check operations

$DataTable = $RestoreCheckDBResult | Select-Object Database, RestoreDurationMinutes, RestoreExecutionDate, CheckDBDurationMinutes, LastGoodCheckDbStatus, LastGoodCheckDb | Out-DbaDataTable

Write-DbaDataTable -SqlInstance 'C&C Server' -InputObject $DataTable -Table autoDB.dbo.AutoRestoreCheckDB -AutoCreateTable

Task 5 is completed with a simple scheduled task on the C&C Server:

$powershellCmd = (Get-Command powershell.exe).Definition

$action = New-ScheduledTaskAction -Execute $powershellCmd -Argument '-NoProfile -Executionpolicy bypass -WindowStyle Hidden -file "D:\DBA\AutoRestoreCheckDb.ps1"'
$trigger = New-ScheduledTaskTrigger -Daily -At 6:00pm
$settings = New-ScheduledTaskSettingsSet -Compatibility WIN8

$STPrincipal = New-ScheduledTaskPrincipal -UserID "Domain\username" -RunLevel "Highest" -LogonType S4U
Register-ScheduledTask -Action $action -Trigger $trigger -TaskName "AutoRestoreAndCheckDb" -Description "AutoRestoreAndCheckDb" -Principal $STPrincipal -Settings $settings

$powershellCmd = (Get-Command powershell.exe).Definition

$action = New-ScheduledTaskAction -Execute $powershellCmd -Argument '-NoProfile -Executionpolicy bypass -WindowStyle Hidden -file "D:\DBA\AutoRestoreCheckDb.ps1"'

$trigger = New-ScheduledTaskTrigger -Daily -At 6:00pm

$settings = New-ScheduledTaskSettingsSet -Compatibility WIN8

$STPrincipal = New-ScheduledTaskPrincipal -UserID "Domain\username" -RunLevel "Highest" -LogonType S4U

Register-ScheduledTask -Action $action -Trigger $trigger -TaskName "AutoRestoreAndCheckDb" -Description "AutoRestoreAndCheckDb" -Principal $STPrincipal -Settings $settings

From here on I can have reports analyzing all the bits and pieces of my process.
Most important, I know now, how long it takes to restore each database and if indeed my backups are valid.
I’m also able to offload the DBCC CHECKDB outside Production servers.

Conclusion

All this is achieved in under 230 lines of code, with the help of the amazing dbatools PS module.

Adding more servers to the process will mean to just add them in a ServerList.txt file that the script consumes. Simple as that!

Now I have the backups and restores verified without moving a finger! How cool is that?

Thanks dbatools and the wonderful people behind it!

For more awesomeness, check out here the full spectrum of commands dbatools has for you!

How to Change your Network Profile in Server 2012R2

Posted by cviorel on June 23, 2017 No comments | This post has 651 views.

Network Profiles were first introduced to allow administrators to configure different firewall profiles based upon what network a user connects to.
Administrators could change the profile by navigating to Network and Sharing Center in control panel and selecting the profile which suits their needs.
In Server 2012, Administrators can no longer change the network profile in Network and Sharing Center, they can view just not change.
You can accomplish this in Power-Shell:
To see the current profile:

Get-NetConnectionProfile

1	Get-NetConnectionProfile

To change the profile:

Set-NetConnectionProfile -InterfaceIndex 12 -NetworkCategory Private

1	Set-NetConnectionProfile -InterfaceIndex 12 -NetworkCategory Private

You can do it with a one line command:

Get-NetConnectionProfile | Set-NetConnectionProfile -NetworkCategory Private

1	Get-NetConnectionProfile \| Set-NetConnectionProfile -NetworkCategory Private

Get SMO version on your server

Posted by cviorel on May 30, 2017 No comments | This post has 789 views.

A quick way to find out what SMO versions are installed:

dir C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo

1	dir C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo

Silently Download and Install SQL Server Management Studio (SSMS)

Posted by cviorel on January 27, 2017 4 comments | This post has 1,908 views.

SSMS is now free and no longer requiring licensing.
It is a separate install and has a more frequent release cycle, usually around 30 days.
To be able to maintain up to date a larger number of SSMS installations I wrote a PowerShell Script for the task.
Of course that a better approach will be to make the download once and then propagate it to other servers, but for now, this works best for me.

<#
.SYNOPSIS
    Silently Download and Install SQL Server Management Studio (SSMS).
.DESCRIPTION
    This script will download and install the latest available SSMS from Microsoft.
.PARAMETER WriteLog
    You want to log to a file. It will generate more than a few files :)
.EXAMPLE
    .\Get-LastSSMS -WriteLog 1
.NOTES
    Author: Viorel Ciucu
    Date: January, 2017
.LINK
 
#>
 
#Requires -RunAsAdministrator
 
[CmdletBinding()]
param (
    [parameter(Mandatory = $false)]
    [int]$WriteLog = 0
)

if(-not $PSScriptRoot) {
    $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
}
 
$msg = ""
$args = @()
$args += "/install /quiet /norestart" 
 
if($WriteLog -eq 1) { 
    $args += "/log SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
    $msg = "InstallationLog: $PSScriptRoot\SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"
}

# Create SSL/TLS secure channel
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Start the download
Write-Host "Download starting! Please wait.."
$ProgressPreference = 'SilentlyContinue'
$Domain = "https://msdn.microsoft.com/en-us/library/mt238290.aspx"
$url = (Invoke-WebRequest -Uri $Domain -UseBasicParsing).Links
$members = ($url | Get-Member).Name

$filter = @()
if ($members -match 'innerHTML') {
    $filter = 'innerHTML'
}elseif ($members -match 'outerHTML') {
    $filter = 'outerHTML'
}

$href = ($url | Where-Object $filter -Match "Download SQL Server Management Studio").href | Select-Object -First 1
$job = Start-BitsTransfer -Source $href -DisplayName SSMS -Destination "SSMS-Setup-ENU.exe" -Asynchronous
 
while (($Job.JobState -eq "Transferring") -or ($Job.JobState -eq "Connecting")) { 
    Start-Sleep 5;
} # Poll for status, sleep for 5 seconds, or perform an action.
 
Switch($Job.JobState) {
    "Transferred" { Complete-BitsTransfer -BitsJob $Job; Write-Output "Download completed!" }
    "Error" { $Job | Format-List } # List the errors.
    default { Write-Output "You need to re-run the script, there is a problem with the proxy or Microsoft has changed the download link!"; Exit } # Perform corrective action.
}
 
# We close running SSMS processes
if (Get-Process 'Ssms') {
    Stop-Process -Name Ssms
}
 
# Install silently
Write-Output "Performing silent install..."
Start-Process -FilePath SSMS-Setup-ENU.exe -ArgumentList $args -Wait -Verb RunAs
 
Write-Output $msg
Write-Output "All done!"

.SYNOPSIS

Silently Download and Install SQL Server Management Studio (SSMS).

.DESCRIPTION

This script will download and install the latest available SSMS from Microsoft.

.PARAMETER WriteLog

You want to log to a file. It will generate more than a few files :)

.EXAMPLE

.\Get-LastSSMS -WriteLog 1

.NOTES

Author: Viorel Ciucu

Date: January, 2017

.LINK

#Requires -RunAsAdministrator

[CmdletBinding()]

param (

[parameter(Mandatory = $false)]

[int]$WriteLog = 0

)

if(-not $PSScriptRoot) {

$PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent

}

$msg = ""

$args = @()

$args += "/install /quiet /norestart"

if($WriteLog -eq 1) {

$args += "/log SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"

$msg = "InstallationLog: $PSScriptRoot\SSMS_$(Get-Date -Format `"yyyyMMdd_HHmm`").txt"

}

# Create SSL/TLS secure channel

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Start the download

Write-Host "Download starting! Please wait.."

$ProgressPreference = 'SilentlyContinue'

$Domain = "https://msdn.microsoft.com/en-us/library/mt238290.aspx"

$url = (Invoke-WebRequest -Uri $Domain -UseBasicParsing).Links

$members = ($url | Get-Member).Name

$filter = @()

if ($members -match 'innerHTML') {

$filter = 'innerHTML'

}elseif ($members -match 'outerHTML') {

$filter = 'outerHTML'

}

$href = ($url | Where-Object $filter -Match "Download SQL Server Management Studio").href | Select-Object -First 1

$job = Start-BitsTransfer -Source $href -DisplayName SSMS -Destination "SSMS-Setup-ENU.exe" -Asynchronous

while (($Job.JobState -eq "Transferring") -or ($Job.JobState -eq "Connecting")) {

Start-Sleep 5;

} # Poll for status, sleep for 5 seconds, or perform an action.

Switch($Job.JobState) {

"Transferred" { Complete-BitsTransfer -BitsJob $Job; Write-Output "Download completed!" }

"Error" { $Job | Format-List } # List the errors.

default { Write-Output "You need to re-run the script, there is a problem with the proxy or Microsoft has changed the download link!"; Exit } # Perform corrective action.

}

# We close running SSMS processes

if (Get-Process 'Ssms') {

Stop-Process -Name Ssms

}

# Install silently

Write-Output "Performing silent install..."

Start-Process -FilePath SSMS-Setup-ENU.exe -ArgumentList $args -Wait -Verb RunAs

Write-Output $msg

Write-Output "All done!"

Hope you’ll find it useful.

Add syntax color to crontab editor

Posted by cviorel on February 24, 2016 No comments | This post has 1,479 views.

You might be wondering why you don’t have syntax coloring when you are editing crontab entries.
This can be solved very simply by specifying your editor before starting crontab:

export EDITOR=vim

1	export EDITOR=vim

The change can be made persistent across the whole system if you add that line on the /etc/bashrc file.
You you only need this change for certain users only, add the line to their local ~/.bashrc files.

DTExec: The package execution returned DTSER_FAILURE (1)

Posted by cviorel on September 11, 2014 No comments | This post has 1,054 views.

What to do when a SQL backup job created with SQL server Maintenance Plans fails with this error?

DTExec: The package execution returned DTSER_FAILURE (1).

1	DTExec: The package execution returned DTSER_FAILURE (1).

Investigate! No useful information in the logs.
Previously that day I dropped a few unneeded databases so maybe the Maintenance Plan was still referencing them. Everything looked good when the Maintenance Plan was opened in SSMS (it was configured to backup “SELECTED” databases), I could not find any reference to the deleted databases. Under the hood, the Maintenance Plan was still trying to backup those deleted databases because when I clicked OK and then hit Save, that Maintenance Plan was refreshed and everything worked just fine.

So always try to find the root cause.

T-SQL to generate backup and restore commands

Posted by cviorel on August 29, 2014 No comments | This post has 632 views.

Hi guys,

Since my day to day job is a DBA, I figured I will share stuff that others might find useful.
For the first time I will share a small script which will help you generate T-SQL commands in order to script the backup/restore process of your SQL Server.

This was tested on Microsoft SQL Server 2012 (SP1) – Express Edition (64-bit). Feel free to send your comments.

/*

This script will list all the databases on the server and generate backup and restore commands for each database, based on the recovery model.
The script will also determine if your SQL Server version supports compressed backups and will generate the commands accordingly.

(C) viorel.ciucu@gmail.com, 28 Aug 2014

*/

DECLARE @backup_location VARCHAR(254) = 'C:\TEMP\' -- full, UNC path
DECLARE @compresion VARCHAR(13) = ''
DECLARE @a nvarchar(max) = ''

DECLARE @CR AS CHAR(1)    -- Carriage Return (CR)
DECLARE @LF AS CHAR(1)    -- Line Feed (LF)
DECLARE @CrLf AS CHAR(2)  -- Carriage Return / Line Feed

SET @CR = CHAR(10)
SET @LF = CHAR(13)
SET @CrLf = @CR + @LF

IF (SERVERPROPERTY('EngineEdition') = 3)
	BEGIN
		PRINT 'Enterprise Edition detected, backup compression IS SUPPORTED!';
		SET @compresion = ' ,COMPRESSION';
	END
ELSE
	BEGIN
		PRINT 'Enterprise Edition NOT detected, backup compression IS NOT SUPPORTED!';
		SET @compresion = '';
	END


SELECT @@SERVERNAME as ServerName, database_id, name, --recovery_model, recovery_model_desc,
--CONCAT('ALTER DATABASE  [', name, '] SET READ_ONLY') AS read_only_command,
CONCAT('BACKUP DATABASE [', name, '] TO DISK = N''', @backup_location+name, '.bak''', ' WITH NOFORMAT, NOINIT', @compresion, ', STATS = 10;') +  @CR +
CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED
THEN
	CONCAT('BACKUP LOG  [', name, '] TO DISK = N''', @backup_location+name, '.trn'' WITH NOFORMAT, NOINIT, STATS = 10') + @LF
ELSE
	''
END AS backup_command, 
--CONCAT('ALTER DATABASE  [', name, '] SET  READ_WRITE WITH NO_WAIT') AS read_write_command,
CONCAT('RESTORE DATABASE [', name, '] FROM DISK = N''', @backup_location+name, '.bak''', ' WITH REPLACE, NORECOVERY;') +  @CR +
CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED
THEN
	CONCAT('RESTORE LOG [', name, '] FROM DISK = N''', @backup_location+name, '.trn''', ' WITH NORECOVERY;') + @LF +
	CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF
ELSE
	CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF
END AS restore_command
FROM sys.databases WITH (NOLOCK) WHERE database_id > 4
ORDER BY name

This script will list all the databases on the server and generate backup and restore commands for each database, based on the recovery model.

The script will also determine if your SQL Server version supports compressed backups and will generate the commands accordingly.

DECLARE @backup_location VARCHAR(254) = 'C:\TEMP\' -- full, UNC path

DECLARE @compresion VARCHAR(13) = ''

DECLARE @a nvarchar(max) = ''

DECLARE @CR AS CHAR(1) -- Carriage Return (CR)

DECLARE @LF AS CHAR(1) -- Line Feed (LF)

DECLARE @CrLf AS CHAR(2) -- Carriage Return / Line Feed

SET @CR = CHAR(10)

SET @LF = CHAR(13)

SET @CrLf = @CR + @LF

IF (SERVERPROPERTY('EngineEdition') = 3)

BEGIN

PRINT 'Enterprise Edition detected, backup compression IS SUPPORTED!';

SET @compresion = ' ,COMPRESSION';

END

ELSE

BEGIN

PRINT 'Enterprise Edition NOT detected, backup compression IS NOT SUPPORTED!';

SET @compresion = '';

END

SELECT @@SERVERNAME as ServerName, database_id, name, --recovery_model, recovery_model_desc,

--CONCAT('ALTER DATABASE [', name, '] SET READ_ONLY') AS read_only_command,

CONCAT('BACKUP DATABASE [', name, '] TO DISK = N''', @backup_location+name, '.bak''', ' WITH NOFORMAT, NOINIT', @compresion, ', STATS = 10;') + @CR +

CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED

THEN

CONCAT('BACKUP LOG [', name, '] TO DISK = N''', @backup_location+name, '.trn'' WITH NOFORMAT, NOINIT, STATS = 10') + @LF

ELSE

END AS backup_command,

--CONCAT('ALTER DATABASE [', name, '] SET READ_WRITE WITH NO_WAIT') AS read_write_command,

CONCAT('RESTORE DATABASE [', name, '] FROM DISK = N''', @backup_location+name, '.bak''', ' WITH REPLACE, NORECOVERY;') + @CR +

CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED

THEN

CONCAT('RESTORE LOG [', name, '] FROM DISK = N''', @backup_location+name, '.trn''', ' WITH NORECOVERY;') + @LF +

CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF

ELSE

CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF

END AS restore_command

FROM sys.databases WITH (NOLOCK) WHERE database_id > 4

ORDER BY name

Page 2 of 12
<
1
2
3
4
5
6
7
8
9
10
...
12
>

cviorel.com

Author Archives: cviorel - Page 2

Register-PSRepository fails on Powershell Core when running behind a corporate proxy

Block Chrome Software Reporter Tool

Enable transmission-daemon logging to file

dbatools – fully automated offsite backups, database restores and integrity checks

Requirements:

Process:

Conclusion

How to Change your Network Profile in Server 2012R2

Get SMO version on your server

Silently Download and Install SQL Server Management Studio (SSMS)

Add syntax color to crontab editor

DTExec: The package execution returned DTSER_FAILURE (1)

T-SQL to generate backup and restore commands

Categories

Author Archives: cviorel - Page 2

Requirements:

Process:

Conclusion

Social

Categories

Tags