SQL Server Backup. DatabaseBackup is the SQL Server Maintenance Solution’s stored procedure for backing up databases. DatabaseBackup is supported on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, and Azure SQL Database Managed Instance.
This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a T-SQL script to find all Backups related history information of SQL Server.
A Database Administrator can use this script for different types of monitoring.
Backup User Profile Folders The purpose of this script is to backup user profile folders prior to enacting folder re-direction to a cloud solution such as OneDrive.The folders are backed up to the local workstation of the currently logged on user.NOTE: This script can be deployed as a package in SCCM or rem. Figure 3.2 Steps for hot backup. The general steps involved in performing hot backup are shown in Figure 3.2. These general steps are used in writing hot backup scripts for Unix and Windows NT. The steps in Figure 3.2 are explained as follows. Step 1—Put the tablespace in the Backup mode and copy the data files. CloudBerry Backup for Linux is a cross-platform cloud backup solution with advanced backup configuration settings and providing total security of data. CloudBerry Backup for Linux With this tool you can backup files and folders to the cloud storage of your choice: it supports more than 20 wide-known cloud storage services.
This script prepared such a way that, you guys can also find information about LSN (Log Sequence Number) information which helps you to identify broken chain.
I was facing a very common issue like Database out of Sync in Log Shipping Setup. I used this script to find out backup related all history and their LSN information.
Using this script, you can find different types of information like Backup media, Type of Backup, Taken Duration.
T-SQL Script, It returns all Backups history information:
2 4 6 8 10 12 14 16 18 20 22 24 | bs.database_name AS DatabaseName WHEN 'D' THEN 'Full' WHEN 'L' THEN 'Transaction Log' ,CAST(DATEDIFF (SECOND,bs.backup_start_date,bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' AS TotalTimeTaken ,CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLSN ,bmf.physical_device_name AS PhysicalDeviceName ,CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) ,bs.server_name AS ServerName FROM msdb.dbo.backupset AS bs ON bs.media_set_id = bmf.media_set_id ORDER BY ,backup_finish_date |
Please visit other related articles...
Introduction
Backup and protect your Linux machine data using scripts bundle provided by IDrive. Protect files during transfer and storage, using 256-bit AES encryption with an optional private key.System/Software Requirements
- Linux (CentOS/Ubuntu/Fedora/OpenSUSE/FreeBSD/Linux Mint) - 32-bit/64-bit
- Perl v5.8 or later
Script Download
Contact support for script bundle download link. Copy or move the downloaded script bundle to a location of your choice in your Linux machine and extract the zip archive.After the extraction of the zip archive, you will find scripts folder in it. Below files will be present in the scripts folder:
Executable Script files:
- account_setting.pl
- archive_cleanup.pl
- Backup_Script.pl
- check_for_update.pl
- edit_supported_files.pl
- express_backup.pl
- help.pl
- job_termination.pl
- login.pl
- logout.pl
- logs.pl
- Restore_Script.pl
- restore_version.pl
- scheduler.pl
- send_error_report.pl
- speed_analysis.pl
- Status_Retrieval_Script.pl
- Uninstall_Script.pl
Other Supported files
- ca-certificates.crt
- Constants.pm
- cron.pl
- dashboard.pl
- Header.pl
- Operations.pl
- readme.txt
- Utility.pl
Other Supported Folders
- Idrivelib - Contains cron startup routine files for all supported platforms and required perl modules.
Other Supported files/folders are used internally by executable script files and users must not try to execute these scripts for any reason.
Provide an appropriate permission (executable permission) to the scripts.
Example:
chmod a+x *.pl.Help Pages
We have provided inbuilt help pages with the help of help script. You may execute below command and select the desired menu option to get a help page for the selected menu option. In case you do not find any relevant menu option, you may select the 'Search By keyword' menu option and provide the desired keyword to look for available information in help pages related to keyword provided by you.$./help.pl
Setup your IDrive account
You need to have an IDrive account to use the script bundle to backup your files. In case you do not have an account, please sign up and create an account here.To set up IDrive script bundle locally with your IDrive account, run the below command and follow the instructions.$./account_setting.pl
Note:- Setting up the script bundle for the first time will ask the user to enter the service path. This path will contain user specific data required to perform operations via script bundle.
- When you try to authenticate an IDrive account using email address, it will prompt the username list which is associated with that email address. Then you have to select the particular username to authenticate.
Note: For some IDrive accounts, while configuring account, 'Create new Backup Location'/'Select from existing Backup Locations' options will appear and Mirror/relative backup type option will not appear.
Working with Scripts
Using IDrive scripts, you can:
- Perform backup/express backup/restore operations
- View progress for backup/express backup/restore/archive cleanup jobs
- Schedule backup / express backup /archive cleanup job
- View log files and more
Log in to your IDrive account
If you are not logged in to your IDrive account while setting up your script bundle, run the below command to login:$./login.pl Note:- Login script is a mandatory script to be executed before performing any operation. This script will authenticate your IDrive account details and will create a secure session for your backups.
- When you try to authenticate an IDrive account using email address, it will prompt the username list which is associated with that email address. Then you have to select the particular username to authenticate.
Edit your backup-set/express backup-set/restore-set, and exclude files
Before starting any backup/express backup/restore operation, the user must add the list of files/folders required for each operation in the backup/express backup/restore set file.To update these details in the backup/express backup/restore set file, you must execute the below command: $./edit_supported_files.plThe menu option will be displayed. Select option 1 to edit the backup set file. Respective file will open in the text editor to edit. Add the files/folders that need to be backed up.Using the same script you can update backup set, express backup set, restore set and exclude files by selecting the desired option.Exclude Files/Folder from your backup setBy using the Exclude option of edit-supported file script, you can exclude files/ folders from being backed up to your IDrive account.- Full Path Exclude
- Partial Path Exclude
- Regex Exclude
Full Path Exclude
To exclude files/folders with full path,
- Run 'edit_supported_files.pl' script and select option 'Edit Your Full Path Exclude List'. The 'FullExcludeList.txt' file will open in a text editor.
- Add full path of the files/folders that you wish to exclude.
- Enter each item in a new line.
- Save and exit.
Example:
Your Backupset contains /home/Documents and if you want to exclude /home/Documents/temp, enter the folder path ie: '/home/Documents/temp' in FullExcludeList file.Partial Path Exclude
To exclude files/folders with partial path,- Run 'edit_supported_files.pl' script and select option 'Edit Your Partial Path Exclude List'. The 'PartialExcludeList.txt' file will open in a text editor.
- Add partial name of the files/folders that you wish to exclude.
- Enter each item in a new line.
- Save and exit.
Example: Your Backupset contains /home/Documents and if you want to exclude all the pst files from this folder like /home/Documents/designtutorials.pst, /home/Documents/new.pst, /home/Documents/James/tutorials.pst etc, then enter 'pst' in PartialExcludeList file.
Regex Exclude
To exclude files/folders based on regex pattern,
- Run 'edit_supported_files.pl' script and select option 'Edit Your Regex Exclude List'. The 'RegexExcludeList.txt' file will open in a text editor.
- Add the regex pattern of the files/folders that you wish to exclude.
- Enter each item in a new line.
- Save and exit.
Example:
Your Backupset contains /home/Folder01 , /home/Folder02, /home/FolderA, /home/FolderB. If you want to exclude all folders/files that contain numeric values in the name, i.e. /home/Folder01, /home/Folder02, then enter 'Folderd+' in RegexExcludeList file.Immediate/Manual backup
You can perform immediate/manual backup using the below command:$./Backup_Script.pl When you run your backup script, it will reconfirm your backup location and allow you to change it. You can now view the graphical progress bar for your data getting backed up. If you want your script not to ask for backup location and not to display progress bar then you can always use “--silent” flag with this command.Note: For some IDrive accounts this script will not provide an option to change 'Backup location'.Immediate/Manual express backup
You can perform immediate/manual express backup using the below command:$./express_backup.pl Using this script, you can backup your Linux machine data to the express device shipped to you. Once the data is backed up, you can ship this express device back to us and within a week your data will be available in your IDrive account. For more details on express, visit IDrive Express™ for Linux.Immediate/Manual restore
You can perform immediate/manual restore using the below command: $./Restore_Script.pl When you run your restore script, it will reconfirm your restore and restore from location and will allow you to change it if required. You can now view the graphical progress bar for your data getting restored.If you want your script not to ask for restore location, restore from location and not to display progress bar then you can always use “--silent” flag with this command.Note: For some IDrive accounts, users will not be able to edit 'Restore From' manually and have to select 'Restore From' location from the list of existing locations.Schedule backup/express backup/archive cleanup job
Run the below command to manage your schedule backup/express backup/archive cleanup job: $./scheduler.plSelect the desired menu option to create, edit, view or delete your schedule backup / express backup / archive cleanup job and follow the instructions. In case you want your schedule job to stop automatically at a scheduled time then set the cut-off time as well, while following the instructions.View progress of scheduled backup/express backup
To view the progress of scheduled backup or express backup operation, run the below command:$./Status_Retrieval_Script.plView/restore previous versions of a file
You can view the list of previous versions of any file and select any version that you want to restore. To retrieve a file with earlier versions, run the below command:$./restore_version.pl Note: For some IDrive accounts, users will not be able to edit 'Restore From' location manually and have to select 'Restore From' location from the list of existing locations.Stop ongoing backup/express backup/restore operations
To stop an ongoing backup or express backup or restore operation, run the below command:$./job_termination.plView/Delete operation logs
You can view or delete the logs for backup, express backup, restore and archive cleanup operations using the below command: $./logs.plArchive cleanup
Archive Cleanup compares the files of your local storage, selected for backup, with the files in your IDrive online backup account. It then deletes the files present in your account but not on your local machine. This feature thus helps you to free up space in your online backup account.To perform archive cleanup run below command: $./archive_cleanup.plWhen you run the script to perform archive cleanup, you can enter a percentage of the total no. of files to be considered for deletion. This percentage based control helps to avoid large-scale deletion of files in your account.Script Backup Sql Server Express
Send error report
You can send error report to IDrive support by running the below command: $./send_error_report.plLog out from your IDrive account
To end the logged in session for your IDrive account, run the below command:$./logout.plYou must logout (optional) from the account to avoid any unauthorized access to your IDrive account. After logout, you need to login again to perform most of the operations.Note: Your scheduled backup / scheduled express backup / periodic cleanup jobs will run even after you log out.
Updating your script bundle
Oracle Script Backup
When a script gets executed, a header appears, which provides details of logged in IDrive account. Same header also displays information on any newly available script bundle. If Software Update Notification is enabled, you will see the message. A new update is available. Run check_for_update.pl to update to the latest package, indicating a new improved version of script bundle has been released.To update to most recent available script bundle please perform the below command and follow the instructions:$./check_for_update.plEdit user details
Script Backup
In case you want to reconfigure your IDrive account locally due to any reason or want to edit user details locally for your configured IDrive account then please perform the below command.$./account_setting.plOnly if your account is already configured in the current machine, you will find the menu which will allow you to reconfigure your IDrive account locally or will allow you to edit the user details locally for your IDrive account. You can configure the following settings:Backup Settings:
- Backup Location: Update the backup location using this option.
- Backup Type: Change the backup type from mirror to relative and vice versa using this option.
- Bandwidth throttle(%): Set the Internet bandwidth to be used by the scripts for backups using this option.
- Failed files(%): By default failed files % to notify backup as 'Failure' is set to 5%. If the total files failed for backup is more than 5%, then backup will be notified as failure. Change the default setting using this option.
- Missing files(%): By default missing files % to notify backup as 'Failure' is set to 5%. If the total files missing for backup is more than 5%, then backup will be notified as failure. Change the default setting using this option.
General Settings:
- E-mail address: Use this option to change the email address provided at the time of account setup locally.
- Ignore file/folder level permission error: If your backup set contains files/folders that have insufficient access rights, IDrive will not backup those files/folders. Hence in such a case, by default, your backup will be considered as 'Failure'. To ignore file/folder level access rights/permission errors, enable this setting.
- Proxy details: If you are behind a proxy address, you should update the proxy settings using this option.
- Show hidden files/folders: Disable this setting to skip hidden files/folders from backup.
- Software Update Notification: Enable this setting to get a notification for available updates in script header.
- Upload multiple file chunks simultaneously: Enable this option to upload multiple file chunks simultaneously to improve overall data transfer speed.
- Service path: Update the service path used by IDrive scripts using this option.
Restore Settings:
- Restore from location: Use this option to change the location from where backups will be restored.
- Restore location: Use this option to change the restore location.
- Restore Location Prompt: Enable this setting if you wish to receive notification about the restore location before starting restore.
Services:
- Start/Restart dashboard service: Use this option to start/restart the dashboard service. This service must be up and running for your machine to be remotely managed from the Dashboard.
- Start/Restart IDrive cron service: Use this option to start or restart IDrive cron service. This service is responsible for all the scheduled jobs to work as expected.
- For some users, while re-configuring or editing account, 'Backup Type' will not be displayed and also while editing 'Restore From' location, list of devices will appear.
- For some users, while editing their account, the ‘Desktop access’ option might appear. Enable this option to access your computer via dashboard.
Test the backup speed
To test the upload speed of the backup, run the following command:$./speed_analysis.plThis script is used to analyze the upload speed for your machine backing up the data to the IDrive servers. A sample text file is uploaded to your IDrive account to calculate the upload speed via IDrive scripts. File backed up for testing will be deleted automatically once the upload speed is determined. Alternatively, it will test the upload speed via speedtest.net and the speed analysis result will be sent to IDrive support based on your confirmation for further investigation.Gta San Andreas Script Backup
Uninstalling your script bundle
Uninstalling the script package from your system will leave the files/folders of your system liable to digital disasters.