SQL Server Database Backup using PowerShell

In this post we will see how we can take backup of databases from SQL Server using PowerShell and schedule it as a daily run Plan.

Step-1 : We will create Powershell Script to take backup of Databases in SQL Server.

SCheduleJob-18

Here is the full Script for your ready refrence.

param( $serverName, $backupDirectory )

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null

$server = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) $serverName

$dbs = $server.Databases

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False })

{

$dbName = $database.Name

$timestamp = Get-Date -format yyyy-MM-dd-HHmmss

$targetPath = $backupDirectory + “\” + $dbName + “_” + $timestamp + “.bak”

$smoBackup = New-Object (“Microsoft.SqlServer.Management.Smo.Backup”)

$smoBackup.Action = “Database”

$smoBackup.BackupSetDescription = “Full Backup of “ + $dbName

$smoBackup.BackupSetName = $dbName + ” Backup”

$smoBackup.Database = $dbName

$smoBackup.MediaDescription = “Disk”

$smoBackup.Devices.AddDevice($targetPath, “File”)

$smoBackup.SqlBackup($server)

“backed up $dbName ($serverName) to $targetPath

}

Save this Script file as ps1 extension.

You can create the script using even notepad.

 

Step 2: We will Create Batch file to call PowerShell scrip and to be used in Windows scheduler.

SCheduleJob-18

Save as .BAT file. Here is the batch script for ready refrence:

powershell -ExecutionPolicy RemoteSigned

-File “C:\User Data\SQL Backup\Tools\SQLServerBackupAllDatabase.ps1”

-serverName “INDEL-AXT5283NB”

-backupDirectory “C:\User Data\SQL Backup”

>> “C:\User Data\SQL Backup\LOG\\%date%.log”

Step 3 : Create a Windows Scheduler

Open Windows Task Scheduler.

Create New Task as shown below :

SCheduleJob-18

Enter Name & Description on General Tab as shown below:

SCheduleJob-18

On Trigger Tab create New Trigger and enter details as shown below :
SCheduleJob-18

On Action Tab Create Action and enter information as shown below : Here Select the batch file created in Step 2.

SCheduleJob-18

In Settings Tab do the setting as shown Below :

SCheduleJob-18

Click on OK to Save the Task and return to Task Scheduler Window.

Here you can see the newly created Task.

SCheduleJob-18

When Task is executed you will find the backup of databases at defined path in the script.

SCheduleJob-26

You can also find Log file at the path defined in batch.

SCheduleJob-27

Thats all for this post, will come up will more information in my up comming posts.

Schedule SQL Job for SQL Database Backup

 

Today we will see how we can schedule to take daily backup of our database.

Before i start explaining the step i am confirming the version of SQL based on which i am writing this post.

Process will remain same may be screen bit different in different versions of SQL.

SCheduleJob-1

At first we will ensure that SQL Server Agent is running, in case it is not right click and Start it.

SCheduleJob-1

Expand the SQL Server Agent Folder, Select Jobs, Right click and select New Job.

SCheduleJob-1

Provide Name & Description to your New Job.

SCheduleJob-1

Select Page Steps & New from Bottom of the page to define Step for your Job.

SCheduleJob-1

Give name to the Step.

Select Type as Transact Script (T-SQL).

Select Database Name in my case i am selecting Demo Database NAV (9-0)

Write the Script as shown below:

SCheduleJob-1

Here is the Script for your ready reference:

{– Script Start

SET QUOTED_IDENTIFIER off

select getdate() “Start Time”

set nocount on
declare @dbname varchar(36),@cmd varchar(255)

declare dbname_cursor cursor
for select name from master..sysdatabases where name = ‘Demo Database NAV (9-0)’
order by name

open dbname_cursor
fetch dbname_cursor into @dbname

while @@fetch_status = 0
begin
DECLARE @DATE VARCHAR(36)
SELECT @DATE = (select CONVERT(char(8),DATEADD(dd,-30,GETDATE()),112))
if DATABASEPROPERTYEX(@dbname,’Status’) = ‘ONLINE’
begin
select @cmd =’backup database [‘+@dbname+’] to DISK=”C:\User Data\SQL Backup\’+@dbname+’.bak” with init’;
print @cmd
execute (@cmd)
end
fetch dbname_cursor into @dbname
end

close dbname_cursor

deallocate dbname_cursor

select GETDATE() “End Time”

–Script End}

Although above script is for multipurpose like if you want to backup all database on your SQL Server, you can make small tweaking and you are done.

If you would like to backup all the databases on a particular SQL Server, then make the following changes to the code above.

Replace following line of code:

for select name from master..sysdatabases where name = ‘Demo Database NAV (9-0)’

with the following line of code:

for select name from master..sysdatabases where name != ‘tempdb’

This will create a backup file for each database on the server, except for the temp database.

Next : Switch to Advanced Page.

Define other parameters as shown in below screen as per your requirement.

SCheduleJob-1

On Selecting OK, you will return to Job window.

SCheduleJob-1

Select Schedule Page:

Define your Schedule as per your requirement.

SCheduleJob-1

Select OK to return to Job Page.

SCheduleJob-1

Select OK to close and save the JOB.

Now our Job is  created and scheduled.

You can see defined Jobs and its Status using Job Activity Monitor.

Before Job is executed

SCheduleJob-1

After executed you can see the Status as.

SCheduleJob-1

Post execution of Job here is the backup file created at defined path in the Script. Above script will overwrite the file on next execution.

If you want to create new file on every execution you need to modify the script for file name by adding timestamp or any other logic you desire.

SCheduleJob-1

I will come up with more information in my upcomming posts.

thats all for this post.

 

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: