Powershell Script to Copy Sql Backup files from one server to another server/ remote server

PowerShell Script for Copying the Latest Backup File:

This script copies local sql backup files to remote folder and also it deletes old backup files (.bak files). We will provide the source folder , destination folder and then it test the destination folder, if folder has all bak files copied to destination folder and then it will delete old backup files..

An overview of Script process looks like below:

  • It will search the backup path for files which are matching a pattern.
  • It Finds the most recent one from the backup path, based on date.
  • It will Copy that most recent file to target location.

Below is the script to copy Latest .bak file from source to destination:

$backuppath = "\\Node1.yourdomainname.com\source" # source path
$destpath = "E:\dest" # Destination path

Get-ChildItem $destpath -recurse -include *.bak -force | remove-item # Remove existing .bak files in destination path

Get-ChildItem -path $backuppath -Filter "*.bak" |
where-object { -not $_.PSIsContainer } |
sort-object -Property $_.CreationTime |
select-object -last 1 | # last 1 means latest backup file
copy-item -Destination (join-path $destpath $_.name)

If you want to copy 10 or 20 latest or recent .bak files from source folder to destination folder then use below script :

$backuppath = "\\Node1.yourdomainname.com\source" # source path
$destpath = "E:\dest" # Destination path
Get-ChildItem $destpath -recurse -include *.bak -force | remove-item # Remove existing .bak files in destination path
Get-ChildItem -path $backuppath -Filter "*.bak" |
where-object { -not $_.PSIsContainer } |
sort-object -Property $_.CreationTime |
select-object -last 20 | # last 1 means latest backup file
copy-item -Destination (join-path $destpath $_.name)

Hope this script helps you to copy the backup files .

SQL DBA Level 2 Support

Level 2 Services will be provided for the Databases receiving L2 as listed above. L2 Services will be provided in accordance with the procedures documented in the SOPs and will include:

  • Incident Management Services

  • Change Management Services

“Incident Management Services” mean the Services to resolve (or break-fix, as applicable). An Incident which has no known resolution will include the following:

The below all comes under L2 Level support Service.

  • Resolve and document Incidents
  • Break-fix and provide workarounds acceptable to the User that reported the Incident which do not require database changes
  • As applicable, escalate incidents requiring immediate attention by using escalation guidelines
  • Maintain Incident information in the Service Management System
  • Carry out daily, weekly and other regular reporting activities for Incidents
  • Maintain documentation for Incidents
  • Update and Maintain Incident information and will responsible for maintaining & updating operation handbook / Runbooks as well.
  • Startups/Shutdowns of database servers
  • Additional Space allocation
  • Object maintenance
  • Job Purging/reruns
  • Initial troubleshooting
  • Cluster ware troubleshooting
  • Set up database auditing
  • Database refresh
  • Standby/Mirror database re-instantiation
  • End User account requests (create, delete, reset password, roles)
  • Elevated privileges requests for existing or new roles
  • Elevated privilege challenge process
  • Non-expiry profile requests
  • Vendor software installation; includes RDBMS and related add-on products
  • Database creation, clones, copies and moves
  • Database upgrades as defined by the Refresh Report, Schema maintenance
  • Patch planning scheduling and installations
  • Database decommissioning
  • Implementing backup solutions
  • Scripts/tool based backup
  • Disaster recovery testing
  • Backup and recovery via various tools(e.g. – TSM, SQL Server Native tools/SQL Server,  Ad-hoc backup and restore)

MS SQL Server Upgrade /Migration

There are many ways to migrate the databases, no matter what database version of SQL Server that we are using, below options are the solutions we can use to upgrade and migrate SQL Server instances:

In-place Upgrade: There will be a down time for the application, since this is completely an automated process and will be helpful only in case of upgrade to be perform on the same server or if we are planning to have databases in the same hardware and a must to have the same configuration and Same instance name of the SQL Server, this is completely managed by the Upgrade wizard from Microsoft upgrades for the SQL Server databases and its components. This type of upgrade is mostly used for non-prod instances, if the installation of upgrade is failed then the roll back time is very high.

Backup Restore method: Here the down time for the application is minimal, where we will be backing up the databases and restoring it to new environment in same location on the physical Windows drives as the existing ones, along with the certificates and also creating a database master key in the master database or the database itself is based on the encryption level at the source, then we would have a log shipping or database mirroring enabled from the existing to new environment, during the cutover phase, we will fail it over the new environment and bring down the existing environment, this is the best practice followed across the industry. Otherwise we will be performing a full backup and restore during the cutover phase for most of the databases, if the size of the database is large, then we will take the differential backup, tail log backup from the source server and restore them to destination server and bring the database to ONLINE state, during the cut over phase. The main disadvantage with this method is that, it needs a lot of manual intervention and the time for preparation work is more than the actual execution phase.

SSIS package: This takes a lot of time for the migration and involves a lot of effort. Create an SSIS package will do copy the data from the existing current environment to a target environment, and then it creates a copy to the new database, to compatible with application. This is a time consuming process as it include writing SSIS package to new database, and then using larger database.

Best practice would be to backup and restore the database from one server to another Server for upgrading the databases, but not all servers can be use this approach. Below are the recommendations to migrate the databases:

Build the Windows Server for SQL and install required Latest SQL server Version on the new build server

Best practices for configuration settings can be implemented while performing the SQL Server upgrade or side-by-side migration.

Restore all the User databases from the Source Server to Target Server .

Stop your SQL agent on Target machine.

Move all your logins from source server to target server using stored proc’s sp_hexadecimal and sp_revlogins

Move all jobs from source to destination server by generating the job scripts on the source server.

Move all your DTS and SSIS packages storing it as file system file and copy it to destination server and make the required configuration changes.

Move all Linked servers from source to target server.

Script out replication setup on source server and move the script to destination server and execute the script to configure replication on the destination server.

Migrate and Configure SSRS, SSAS instance databases.

Also need to understand the new features impact (like SQL 2008 Backup compression strategies, Policy Base management, Resource Governor etc..)

Check Long running queries / any performance issues etc.,

Also move Maintenance DBA jobs to the new environment

Install .NET 3.5 Framework on Windows Servers 2012\ 2016\ 2019 without DVD

In latest Windows Server Versions by default we get .NET Frame Work 4.6 Installed on the server although .NET Frame Work 3.5 is Available it will not get installed .If you want to install .NET Frame Work 3.5 in latest Windows Servers we can do this in following ways like :

  • From the Server Manager : Click on Add Roles and Features -> Click Features -> Click NET Framework 3.5 Features
  • From DISM command: DISM /Online /Enable-Feature /FeatureName:NetFx3 /All
  • From PowerShell : Add-WindowsFeature NET-Framework-Core -Source DriveName:\sources\sxs

OR

If you have internet Access working in your Windows Server then it can be done by configuring the server to use Windows Update as a source without providing the Source path. This worked for me.

Here Windows Update has download the .NET Frame Work 3.5 installation files, but the server is configured not to use Windows Update (common for managed servers). To get Installed with Windows Update we have to Enable the option and click the Check box option ‘ Download repair content and optional features directly from Windows Update instead of WSUS ‘.

For approaching above go through below steps :

1. Start the Local Group Policy Editor or Group Policy Management Console (WIN+R and type gpedit.msc).
2. Expand Computer Configuration Folder–> then click expand Administrative Templates Folder–> then select System.
3. Double click to open Specify settings for optional component installation and component repair Group Policy setting, and then select click Enabled.
4. Also click Enable for Contact Windows Update directly to download repair content instead of Windows Server Update Services (WSUS) checkbox.

Now go to Add Roles and Features Wizard and select enabling checkbox .NET Framework 3.5 and get it installed successfully.