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.



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.



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 :



Enter Name & Description on General Tab as shown below:



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


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



In Settings Tab do the setting as shown Below :



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

Here you can see the newly created Task.



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



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



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.



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



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



Provide Name & Description to your New Job.



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



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:



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.



On Selecting OK, you will return to Job window.



Select Schedule Page:

Define your Schedule as per your requirement.



Select OK to return to Job Page.



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



After executed you can see the Status as.



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.



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

thats all for this post.

 

Database Schema Synchronization

Microsoft Dynamics NAV 2015 introduces several improvements to the table schema synchronization process that improve performance and make it more explicit and controllable. The improvements give you with the flexibility to decide when and how the table changes are synchronized with corresponding tables in the SQL Server.

In Microsoft Dynamics NAV 2015, changes to the database schema are no longer synchronized automatically to the database when a client connects to a Microsoft Dynamics NAV Server instance. Instead, you specify when to synchronize schema changes in the development environment or the Microsoft Dynamics NAV 2015 Administration Shell.

For example, when you change a table object and compile the table, you can choose to synchronize the change to the database immediately or later. In a production environment, you apply changes to the database schema by running the Sync-NAVTenant Windows PowerShell cmdlet with the Mode parameter set to the relevant synchronization mode.

The schema synchronization design provides the following features and benefits:

• On-demand schema synchronization and monitoring As a developer or system administrator, from the development environment or Microsoft Dynamics NAV 2015 Administration Shell, you control when to perform the schema synchronization and how to synchronize table changes.This enables you to plan and schedule the schema synchronization on a case-by-case basis. Additionally, you can monitor the progress and state of the business (tenant) database at any time.


• Non-blocking and increased productivity Schema synchronization has become more granular. You perform schema synchronization for a single table or for all tables at the same time. When one table is being synchronized, you are no longer blocked from making changes to other tables.


• Capability to provide additional instructions in upgrade codeunits By using upgrade codeunits, you can provide instruction for the schema synchronization to perform additional tasks, such as automatically moving or copying data into upgrade tables, validating the table changes before applying them, or forcefully applying them when it is required.

Please check my earlier post on this topic for more detailed information.

Schema Synchronization in Microsoft Dynamics Navision 2015

Data Upgrade Codeunit in Navision 2015 Part-1

Data Upgrade Codeunit in Navision 2015 Part-2

Database Function

EXPORTDATA Function (Database)

Exports data from the Microsoft Dynamics NAV database to a file. The data is not deleted from the database.

Syntax

[Ok:=] EXPORTDATA(ShowDialog, FileName[, IncludeApplicationData][, IncludeGlobalData][, CompanyRecord])

Parameters

ShowDialog

Type: Boolean

Specifies if you want to display a dialog box where the user can confirm the action.

FileName

Type: Text

Specifies the name and location of the file that the data must be exported to. The file must have the .navdata extension.

IncludeApplication

Type: Variable

Specifies if you want to export the application objects.

Create a variable of type Boolean to specify this parameter.

IncludeApplicationData

Type: Variable

Specifies if you want to export the data that defines the application in the database. This includes the permissions, permission sets, profiles, and style sheets.

Create a variable of type Boolean to specify this parameter.

IncludeGlobalData

Type: Variable

Specifies if you want to export global, non-company specific data.

Create a variable of type Boolean to specify this parameter.

CompanyRecord

Type: Record

Specifies the company or companies that must be imported.

You can find the usage of this function in Std. Page Object 9901 [Export Data]


IMPORTDATA Function (Database)

Imports data from a file that has been exported from a Microsoft Dynamics NAV database.

Syntax

[Ok:=] IMPORTDATA(ShowDialog, FileName[, IncludeApplicationData][, IncludeGlobalData][, CompanyRecord])

Parameters

ShowDialog

Type: Boolean

Specifies if you want to display a dialog box where the user can confirm the action.

FileName

Type: Text

Specifies the name and location of the file that must be imported. The file must have been exported from a Microsoft Dynamics NAV database.

IncludeApplicationData

Type: Variable

Specifies if you want to import the data that defines the application in the database. This includes the permissions, permission sets, profiles, and style sheets.

Create a variable of type Boolean to specify this parameter.

IncludeGlobalData

Type: Variable

Specifies if you want to import global, non-company specific data.

Create a variable of type Boolean to specify this parameter.

CompanyRecord

Type: Record

Specifies the company or companies that must be imported.

You can find the usage of this function in Std. Page Object 9900 [Import Data]


DATAFILEINFORMATION Function (Database)

Specifies data from a file that has been exported from a Microsoft Dynamics NAV database.

Syntax

[Ok:=] DATAFILEINFORMATION(ShowDialog, FileName, Description, HasApplication, HasApplicationData, HasGlobalData, tenantId, exportDate, CompanyRecord)

Parameters

ShowDialog

Type: Boolean

Specifies if you want to display a dialog box where the user can confirm the action.

FileName

Type: Text

Specifies the name and location of the file that you want to read information from. The file must have been exported from a Microsoft Dynamics NAV database.

HasApplication

Type: Variable

Specifies if the file contains application objects.

Create a variable of type Boolean to specify this parameter.

HasApplicationData

Type: Variable

Specifies if the file contains the data that defines the application in the database. This includes the permissions, permission sets, profiles, and style sheets.

Create a variable of type Boolean to specify this parameter.

HasGlobalData

Type: Variable

Specifies if the file contains global, non-company specific data.

Create a variable of type Boolean to specify this parameter.

tenantId

Type: Variable

Specifies the tenant ID of the database that the data was exported from.

Create a variable of type Text to specify this parameter.

exportDate

Type: Variable

Specifies the date and time when the data was exported.

Create a variable of type DateTime to specify this parameter.

CompanyRecord

Type: Record

Specifies the company or companies in the file.

You can find the usage of this function in Std. Page Object 9900 [Import Data]