white paper - backup using powershell
Post on 02-Jun-2018
214 Views
Preview:
TRANSCRIPT
-
8/10/2019 White Paper - Backup Using Powershell
1/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 1 Modified: 12/19/2014 10:44 PM
White Paper on
Backup monitoring - PowerShell
Submitted By: Nikhil Vyas1/26/2014
-
8/10/2019 White Paper - Backup Using Powershell
2/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 2 Modified: 12/19/2014 10:44 PM
Table of Contents
Control ........................................................................................................................................................ 3
Revision History ......................................................................................................................................................3
Related Documents.................................................................................................................................................3
Introduction ............................................................................................................................................... 4
Procedure ................................................................................................................................................... 5
Conclusion .................................................................................................................................................. 11
References ................................................................................................................................................. 11
-
8/10/2019 White Paper - Backup Using Powershell
3/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 3 Modified: 12/19/2014 10:44 PM
Control
Revision History
Date Version Description Author
1/26/2014 1.0 Initial Draft Nikhil Vyas
Related Documents
No. Title Description Location
-
8/10/2019 White Paper - Backup Using Powershell
4/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 4 Modified: 12/19/2014 10:44 PM
Introduction
One of the most evitable tasks for running a business is making sure we
have the backup of the data in place. The last thing we would like tohappen is our database going down and on that server is our clients
customer list or say banking transactions. Without a backup, we cannot
recover the database and the business goes under. We need to make
sure that our databases are being backed up. We also need to be able
to check and confirm that every database on every server is being
backed up. All this is to be done in a fairly quick time.
Whats PowerShell?
Before getting into the procedure let me reiterate what is powershell for you.
Windows PowerShell isMicrosoft's task automation and configuration
management framework, consisting of acommand-lineshell and
associatedscripting language built on.NET Framework.PowerShell provides fullaccess toCOM andWMI,enabling administrators to perform administrative tasks
on both local and remote Windows systems as well asWS-
Management andCIM enabling management of remote Linux systems and
network devices.
You can read more about it here http://en.wikipedia.org/wiki/Windows_PowerShell
http://en.wikipedia.org/wiki/Microsofthttp://en.wikipedia.org/wiki/Command-line_interfacehttp://en.wikipedia.org/wiki/Shell_(computing)http://en.wikipedia.org/wiki/Scripting_languagehttp://en.wikipedia.org/wiki/.NET_Frameworkhttp://en.wikipedia.org/wiki/Component_Object_Modelhttp://en.wikipedia.org/wiki/Windows_Management_Instrumentationhttp://en.wikipedia.org/wiki/WS-Managementhttp://en.wikipedia.org/wiki/WS-Managementhttp://en.wikipedia.org/wiki/Common_Information_Model_(computing)http://en.wikipedia.org/wiki/Windows_PowerShellhttp://en.wikipedia.org/wiki/Windows_PowerShellhttp://en.wikipedia.org/wiki/Windows_PowerShellhttp://en.wikipedia.org/wiki/Common_Information_Model_(computing)http://en.wikipedia.org/wiki/WS-Managementhttp://en.wikipedia.org/wiki/WS-Managementhttp://en.wikipedia.org/wiki/Windows_Management_Instrumentationhttp://en.wikipedia.org/wiki/Component_Object_Modelhttp://en.wikipedia.org/wiki/.NET_Frameworkhttp://en.wikipedia.org/wiki/Scripting_languagehttp://en.wikipedia.org/wiki/Shell_(computing)http://en.wikipedia.org/wiki/Command-line_interfacehttp://en.wikipedia.org/wiki/Microsoft -
8/10/2019 White Paper - Backup Using Powershell
5/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 5 Modified: 12/19/2014 10:44 PM
Procedure
There are many ways through which one can back up the database but in this
case I would like to bring to notice the effectiveness of powershell in doing so.
Let us understand how PowerShell and also the SQL snap-ins help us to make the
task much easier.We can only do this on a machine where SQL snap-ins is installed. If we have the
SQL 2008 or later installed, we should be good to go.
Open the SQL snap-ins using the below command on powershell. It doesn not
matter which version of Powershell we are using.The below command areindependent of the version on Powershell
Add-pssnapin SqlServerProviderSnapin100
In this case I will query a localized installation of SQL Server. We can change
out localhost\DEFAULT for the SQL server we are using. Use DEFAULT if it
is not a named instance (example - Server SQLSERVER and instance name
as NORTHWIND would be would be SQLSERVER\NORTHWIND)
Now, we can display the list of database on the SQL Instance and get the
information about their last backup date using the below command
dir SQLSERVER:\SQL\SQLSERVER\NORTHWIND\Databases | Select Name,LastBackupDate | Out-GridView
(Server is SQL SERVER and DB Instance is NORTHWIND)
o
The result will fetch you something like this
-
8/10/2019 White Paper - Backup Using Powershell
6/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 6 Modified: 12/19/2014 10:44 PM
Name LastBackupDate
AdventureWorks2008R2 12/27/2013 11:53
Report Server 12/26/2013 1:53
SQLInfo 11/26/2013 10:23BankDB 11/5/2013 1:53
Please not that the MASTER, MODEL, MSDB databases will not be displayed
with the above commands since they are the system database. System
Databases are hidden and hence we would need to use the Force
parameter as
o
dir -force SQLSERVER:\SQL\SQLSERVER\NORTHWIND\Databases |
Select Name, LastBackupDate | Out-GridView
Name LastBackupDate
AdventureWorks2008R2 12/27/2013 11:53
Report Server 12/26/2013 1:53
SQLInfo 11/26/2013 10:23
BankDB 11/5/2013 1:53
master 12/7/2013 0:00
model 12/8/2013 0:00
msdb 12/8/2013 1:05
As we see that we are getting the information about last backup taken but
thing to note is that the information provided was the last full backup and
no information provided for differential backup. Also, if we run the above
commands and then backup and again run the command, we would not see
the change in results. This is because the data is cached every time we are
running the command. The solution is to run the refresh command.
-
8/10/2019 White Paper - Backup Using Powershell
7/11
-
8/10/2019 White Paper - Backup Using Powershell
8/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 8 Modified: 12/19/2014 10:44 PM
ELSE {"DIFF"}};width=25},
@{Label="Days Since Last Backup";Expression={IF
($_.LastBackupDate -eq "01/01/0001 00:00:00") {"Never Backed
Up!"}
ELSEIF ($_.LastDifferentialBackupDate -gt $_.LastBackupDate) {((Get-
Date) - $_.LastDifferentialBackupDate).Days}ELSE {((Get-Date) - $_.LastBackupDate).Days}};width=25}
if ($Northwind)
{
dir -force $location | where-object {$_.Name -eq $Northwind;
$_.Refresh()} |
format-table $DisplayResults
}
else
{
dir -force $location | where-object {$_.Name -ne "tempdb";
$_.Refresh()} |format-table $DisplayResults
}
}
Now to return good, relevant information, we simply call this function along with
the SQL instance name as shown here. Get-DatabaseInfo Northwind
-
8/10/2019 White Paper - Backup Using Powershell
9/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 9 Modified: 12/19/2014 10:44 PM
Now when we have the information related to the entire database and
their backup information, we would need the same information to you in
the mail so that we do not have to run the command every time we need
the information. This can be achieved easily by fetching the data into a table in anyone of
your database. Say, we have the table called DBBackup, the following
command will fetch us the results of all the backup information in table and
from there we can send the information in a mail.
o
SELECT*FROMdbo.DBBackup
The below command will actually help us send the email to desired
recipient. We will use the Invoke-sqlcmd .The Invoke-Sqlcmdcmdlet lets you
run your sqlcmdscript files in a Windows PowerShell environment.
-
8/10/2019 White Paper - Backup Using Powershell
10/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 10 Modified: 12/19/2014 10:44 PM
$FailedBackups = invoke-sqlcmd@params
-Query"Select ServerName,DatabaseName
, case LastAbsoluteBackupDate
when '1900-01-01 00:00:00' then 'Never Backed Up'else
convert(varchar(20), LastAbsoluteBackupDate, 120)
end as
LastBackup
,case LastAbsoluteBackupDate
when '1900-01-01 00:00:00' then 'Never Backed Up'
else
convert(varchar,IncrementalBackup)
end as IncrementalBackupfrom dbo. DBBackup where IncrementalBackup > 0" | convertTo-Html-
Head$style -PropertyServerName, DatabaseName, LastBackup,
IncrementalBackup | out-string;
We can then provide the SMTP information
Send-MailMessage-To"abc@xyz.com"
-Subject"Alarm Database not backedup"
From"me@me.com"
-SmtpServer"mysmtpserver"
-Body$FailedBackups BodyAsHtml
We can get the most out of it by scheduling it to the desired timings usingthe SQL Server Agent which will help us avoid any issues prior to any
business impact.
-
8/10/2019 White Paper - Backup Using Powershell
11/11
Backup monitoring - PowerShell
Ver: 1
2011 Accenture. All Rights Reserved. 11 Modified: 12/19/2014 10:44 PM
Conclusion
Although getting information on backup can be accomplished by
querying the database creating a function and calling it at the required
time( We can even deliver the same to mailbox) however the biggest
difficulty in getting that dont through SQL is that It will not provide
information of the database which are not backed up. Also, since
multiple databases are involved here due to the indexing and
maintenance strategiesthe data retrieval will be far slower than doing
it through the Windows Powershell.
References
1.Wikipedia
2.Microsoft - MSDN
top related