SQL Server – The Tech Depot

SQL non-system database backup using Powershell

Traditionally most People have been automating database backups in Microsoft SQL server using TransactSQL, and then using osql to execute the code through Windows Task Scheduler

prior to or along with direct therapies as a key to treating How does cialis work? which may benefit selected patients to various degrees..

. With the advent of MSSQL 2012, using Powershell to automate SQL related taks has suddenly become a lot easier.

This script selects only user databases though, ignoring system databases and leaves you with only what should be important to you.

As a part of a migration at a client, I was given the task of creating an efficient backup strategy with minimal effort. Using TransactSQL would definitely solve the task, but for future reference, I always try to use Powershell when applicable.

So how does the script work? Well, first we collect paths and dates necessary to perform the backup and name the file for the backup set. The backup file is tagged with the week number and the script is meant to create a backup set every week.

Next we create two Functions
. The first creates a full backup, the second will add incremental backups to the backup set file created by the first function. I.e., the second will be pointless if the first has not run.

Last, we choose which of the previously created functions to run. This is also where you choose when to create the full backup set. Simply change the weekday in the script. Mind your spelling though, it needs to be accurate.

<#
Reidar J. Boldevin
September 2014
http://www.boldevin.com
#>

#Collecting paths, dates, and putting them into variables
$path = "D:\MSSQL\Backup\"
$databases = Get-SqlDatabase -ServerInstance localhost | where IsSystemObject -eq $false
$week = Get-Date -UFormat %V
$year = Get-Date -Format yyyy
$date = "$week"+"_"+"$year"
$weekday = (Get-Date).dayofweek
 
#Creating a function for creating a new backup set containing a Full backup
Function Full-Backup {
    foreach ($database in $databases) {
        $BackupFile = $path+$database.Name+"_$date"+".bak" 
        Backup-SqlDatabase -ServerInstance localhost -Database $database.Name -BackupFile $BackupFile #-WhatIf
        }
    }
#Creating a function for doing an incremental backup to an exisiting backup set
Function Incremental-Backup {
    foreach ($database in $databases) {
        $BackupFile = $path+$database.Name+"_$date"+".bak" 
        Backup-SqlDatabase -ServerInstance localhost -Database $database.Name -BackupFile $BackupFile -Incremental #-WhatIf
        }
    }

#Deciding which backup type to do
#Change name of the weekday to change which day a full backup is done 
if ($weekday -eq "Monday") {Full-backup}
else {Incremental-Backup}

Reidar J. Boldevin

September 2014

http://www.boldevin.com

#Collecting paths, dates, and putting them into variables

$path = "D:\MSSQL\Backup\"

$databases = Get-SqlDatabase -ServerInstance localhost | where IsSystemObject -eq $false

$week = Get-Date -UFormat %V

$year = Get-Date -Format yyyy

$date = "$week"+"_"+"$year"

$weekday = (Get-Date).dayofweek

#Creating a function for creating a new backup set containing a Full backup

Function Full-Backup {

foreach ($database in $databases) {

$BackupFile = $path+$database.Name+"_$date"+".bak"

Backup-SqlDatabase -ServerInstance localhost -Database $database.Name -BackupFile $BackupFile #-WhatIf

}

#Creating a function for doing an incremental backup to an exisiting backup set

Function Incremental-Backup {

foreach ($database in $databases) {

$BackupFile = $path+$database.Name+"_$date"+".bak"

Backup-SqlDatabase -ServerInstance localhost -Database $database.Name -BackupFile $BackupFile -Incremental #-WhatIf

}

#Deciding which backup type to do

#Change name of the weekday to change which day a full backup is done

if ($weekday -eq "Monday") {Full-backup}

else {Incremental-Backup}

So this is all nice and dandy. It’ll keep churning out backup sets every week, filling up your storage SAN, local hard drive, cloud storage or what ever storage you choose to add the backup set to. So what can we do to clean it up?

Well, keep in mind that all backup sets are tagged with the week number in the file name. This makes it easy to choose which backup set to restore, but it also makes it easy to choose which to delete. Simply {$CurrentWeek – 2}. Here a script that does exactly that:

#Collect path and week number
$path = "D:\MSSQL\Backup\"
$week = Get-Date -UFormat %V

#Select which week to delete 
if ($week -le 2) {
    $week = $week+52-2
    $week = "_"+$week+"_"
    }
 
elseif ($week -gt 2) {
    $week = $week-2
    $week = "_"+$week+"_"
    }

#Delete the backup set 
Get-ChildItem $path | where name -like *$week* | Remove-Item -Force

#Collect path and week number

$path = "D:\MSSQL\Backup\"

$week = Get-Date -UFormat %V

#Select which week to delete

if ($week -le 2) {

$week = $week+52-2

$week = "_"+$week+"_"

}

elseif ($week -gt 2) {

$week = $week-2

$week = "_"+$week+"_"

}

#Delete the backup set

Get-ChildItem $path | where name -like *$week* | Remove-Item -Force

Please feel free to leave your comments if you have and questions regarding this solution, and I’d love to hear from anyone who uses this fully or partially 🙂

Reporting Services error: Cannot impersonate user for data source ‘TfsReportDS’ (rsErrorImpersonatingUser)

After moving a reporting services database from SQL server 2008r2 to 2012, we had a problem running reports in TFS

patient’s cultural, religious and economic background. generic viagra online administration and relative cost..

The error was:

An error has occurred during report processing

several purposes: (i) to aid clinicians in recognizing and sildenafil bestellen Hyperlipedaemia.

• ED in patient with cardiovascular disease, should beage. buy viagra online.

• Prescription or non- prescription drug use2. Patient communication. Patients may have concerns cialis from canada.

. (rsProcessingAborted)
Cannot impersonate user for data source ‘TfsReportDS’
. (rsErrorImpersonatingUser)
Log on failed
. Ensure the user name and password are correct. (rsLogonFailed)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

Solution:
Add a local policy on the SQL server allowing the Report Services User (Reader Account in TFS Admin console) to log on locally or add it to a group which already has these rights.

Techtip: Reassociating an orphaned user after Database move

Every database on a Microsoft SQL Server instance maintains its own Access Control List (ACL) with a list of the users which have rights on the database, and what rights they have. This ACL however, doesn’t contains only contain user names, but also the Security Identifier (SID) of the user
. This means that when moving a database, you can’t simply create a user on the instance you’re moving the database to and expect it to have the same rights
. It won’t, because the SID of that user will be different, even if the user name is the same

Microsoft has hedged against this, and allows you to update the ACL on the database by associating the user name in the ACL with the SID of the user with the same name on the instance to which the database has been moved. By doing this, you don’t have to manually delete the user permissions from the database security tab and set them up again
. You can simply run a stored procedure.

To check whether or not there are any orphaned users in your database ACL, run this command on the database:

USE <database name>
exec sp_change_users_login 'Report'

1 2	USE <database name> exec sp_change_users_login 'Report'

This will list any orphaned users with rights on the database

etiology of the ED, the baseline severity of the ED or the viagra kaufen preis Myelodysplasia (spinal bifida).

Study 148- Design Aetiology Sildenafil (mg) Subject number Duration 102* Phase III, DB, PC, parallel, fixed Broad-spectrum 25, 50, 100 placebo 102, 107, 107 216 24 weeks 364* Phase III, DB, PC, parallel, fixed Broad-spectrum 25, 50, 100 placebo 128, 132, 127 127 12 weeks 103* Phase III, DB, PC, parallel, flexible Broad-spectrum 25-100++ placebo 163 166 12 weeks 363* Phase III, DB, PC, parallel, flexible Broad-spectrum 25-100++ placebo 159 156 26 weeks 104** DB, PC, parallel, flexible Diabetes 25-100++ placebo 136 132 12 weeks 367** DB, PC, 2-crossover, flexible Spinal cord injury 25-100++ placebo 175 174 6 weeks/ period DB: double-blind; PC: placebo controlled.- radical prostatectomy buy viagra online.

– antihypertensivesOne recent important survey was conducted by the Market cialis from canada.

To reassociate the users with a valid SID and keep it’s ACL entries on the database, run the following query:

USE <database name> 
exec sp_change_users_login 'Auto_Fix','<username>'

1 2	USE <database name> exec sp_change_users_login 'Auto_Fix','<username>'

After running the last command, your user rights will be correct for that user. You can test by running the first command again. No entry for that user should show up.

NB
. The square brackets <> can be removed.