How make MS SQL full database backups



Creating Database Backup Plans 

1. Create the folders to store the Database backups in the drive, we want to store with the following folders.(Ex: C:\SQL_backup\Full)

2. Open SQL Server Management Studio and connect using windows credentials.

3. Click Management  and select Maintenance Plans.
 
4. Right-Click on Maintenance Plans and select New Maintenance Plans , Give naming Convention as Full_DB_Backup.


5. In Left side of Server Management Studio there will be a Tool Box within Maintenance Plan Tasks



6. Drag and drop the Back Up Database Task and Maintenance Clean Task in the Full_DB_Backup Design area.

7. Right Click on the Back Up Database Task and select Edit.
 

8. Select the Type of Backup you want to do as Full 

9. Select the Database In Database(s)  in these database list (Ex: TestDB.)
  

10. Select Create a Backup file for every database, select Create a sub-directory for each database

11. Give the path where we want to store the database backup as C:\SQL_backup\Full and give the Backup file extension as bak and Click OK

 

12. Right Click on the Maintenance Cleanup Task  and select Edit 

13.  Select Search folders and delete files based on an extension 

14.  Give the Folder Path  C:\SQL_Backup\Full  and give a file extension as bak in the file extension box and select Include first-level sub-folders 

15. File Age:  Give the desired file retention period that how many days of backup you wanted to keep in the backup folder. (Ex: 2 Weeks)
  

16. Click OK 

17. Right Click on the Back Up Database Task,  select Add Precedence Constraint,  Select Back Up Database Task in From and Maintenance Cleanup Task in To from the Control Flow and click OK
 

18. Now you will see a link between Back Up Database Task and Maintenance Cleanup Task. Press Ctrl+S to save the task


19. Go to the SQL Server Agent in the SQL Server Management Studio, Select the Full_DB_Backup and Right Click and select Properties  in the properties sheet select Schedules and click New

 

20. Give some name in the name so that we can identify the Job Schedules

21. Select Weekly in frequency, Recurs every  in 1 week, and select Sunday 

22. Select 11:30:00 PM in Daily frequency and click OK.
  

23. Click Ok in Job properties.

Keywords: Backups, Full Backup, MSSQL Backups, 

Comments

Post a Comment

Popular posts from this blog

Observium: Configuring Microsoft Windows 2008 Server SNMP Agent

Image
1 . Open Control Pannel. 2. Right click on “ Programs & Features ” and click on “ Turn Windows Features on or off" 3 . Expand “ SNMP Services ” and select “ SNMP Service ” click Next 4. Click Install 5. Close  Programs & Features ” 6. Open Ädministraive Tools and clicket Services 7. Expand Configuration and select Services,  Select SNMP Service , right click and select properties 8 . Select the “ Agent ” tab 9. Type the contact name responsible for this server or email address 10 . Type the location eg, Hyderabad,India. 11 . Tick the following  Physical Datalink and subnetwork  SNMP Trap Service - Agent 12 . Select the “ Traps ” tab 13 . Type in the community string you are using in your environment and select “ Add to list ” 14 . Click “ Add ” 15 . Type the IP of the Observium ServerSNMP Trap Service - Traps 16 . Select “ Security ” tab 17 . Ensure “ Send ...
Read more

AWS: Introduction to AWS (Amazon Web Services)

Image
Amazon Web Services (AWS) Considering that you don't have an account with AWS ( Amazon Web Services) this Demo is divided into 5 steps 1. Creating/Sign Up for AWS account. 2. Creat ing an IAM user . 3. Creat ing a secure key pair. 4. Creat ing a Virtual Private Cloud (VPC) 5. Creat ing a security group for access. 1. Creating/Sign Up for AWS account When we sign up for the AWS account, we get automatic  access for all the services in the AWS, which includes the Amazon EC2 and we are charged for only the services which we chose and use. With Amazon EC2 we will pay for that what we use and if we are a  new AWS customer and we can get started with Amazon EC2 for free. If we have an AWS account already, we can skip the next task or else we can continue with the account creation. You can check the AWS Free Tier information here http://aws.amazon.com/free/ To create an AWS account, we needed to access the URL a). Connect to the URL ht...
Read more

System/Server Health Status using PowerShell

Image
System/Server Health Status using PowerShell Support team or System Administrators who are willing to get the system health check status can use this script. This script has been written in Powershell and it can generate   reports   in HTML format. This script can give you below information: Hardware: To Get Manufacturer, Model, No.   Of   Logical Processors, Installed Memory.   Code:  Get-WmiObject -class Win32_ComputerSystem -ComputerName $Computername | Select-Object Name,Domain,Manufacturer, Model,NumberOfLogicalProcessors,@{ Name = "Installed Memory (GB)" ; Expression = { "{0:N0}" -f( $_.TotalPhysicalMemory / 1gb ) } }            CPU Details: To Get  CPU speed, Current CPU Usage in %,  Code: Get-WmiObject win32_processor | Select-Object SystemName,@{ Expression = { $_.MaxClockSpeed...
Read more