03_Backup And Restore Database In SQL Server.pptx

This is a step by step backup and restore sql database

1. Backup a nd Restore Database In SQL Server

2. In this topic, you will learn: T o create a copy of data that can be recovered in the event of a primary data failure To backup duplicate copies of critical data to be able to restore when needed and to protect organizations from data loss . Backups protect against human errors, hardware failure, virus attacks, power failure, and natural disasters. Backups can help save time and money if these failures occur.

3. Overview SQL Server is a widely used relational database management system. It is used to store and retrieve data in many large organizations. Microsoft SQL Server recommends that users periodically back up the database to prevent data loss or corruption.  MS SQL Server supports three files, Primary Database File (MDF), Secondary Database File (NDF), and Log File (LDF). Here, step-by-step, we will create SQL Server backup and restore with SSMS and the Transact-SQL command.

4. Benefits – Create Backup and Restore Database in SQL Server Creating a backup is always best to recover data in case of data corruption issue. As mentioned previously, SQL Server always recommends that users backup their database and there are two methods available for this provided by Microsoft. First, SQL Server management studio and second T-SQL command. If you have a backup of your database, you can easily restore . bak file in SQL Server. Overall backup and restore is the best option to protected data from damage.

5. How to Backup Microsoft SQL Server Database? Here, we will discuss two methods to take backup of SQL Database using SSMS and T-SQL. Follow the steps that are mentioned in the below section . Backup SQL Database With SQL Server Management Studio Step 1 .   Open SSMS and connect to the SQL Server Step 2 . Expand  Databases  and select the required database Step 3 .  Right click  on the database >>  Tasks  >>  Backup

6. How to Backup Microsoft SQL Server Database ? (cont.) Step 4 . In  Back Up Database  window, select the  Backup Type  as  Full  and under  Destination , select  Back up to  :  Disk

7. How to Backup Microsoft SQL Server Database? (cont.) Step 5 . Select the  Remove  button Step 6 . Click on  Add  button to select the destination and name for the database backup file Step 7 . Select the required folder for the backup file and enter the file name with a  . bak  extension Step 8 . Click  OK  to end the backup process.

8. How to Backup Microsoft SQL Server Database? (cont.) Backup of SQL Server Database With Transact-SQL Create Full SQL Backup to Disk BACKUP DATABASE databasename TO DISK = ' filepath ' GO Ex: BACKUP DATABASE [Ch03_TinyCollege]TO DISK = 'C:\dev\Ch03_TinyCollege.BAK' Create Full Backup to Multiple Disk Files BACKUP DATABASE AdventureWorks TO DISK ='C:\AdventureWorks_1.BAK', DISK ='D:\AdventureWorks_2.BAK', DISK ='E:\AdventureWorks_3.BAK' GO

9. How to Backup Microsoft SQL Server Database? (cont.) This is the complete process of creating SQL Server Database backup. Next, let’s discuss the process to restore . bak file in SQL Server by which you can learn how to backup and restore database in SQL Server easily.

10. How to Restore Database From BAK File SQL Server? With SSMS and the T-SQL command, we can easily restore backup file (. bak ). Follow the steps listed below to perform this task . Restore SQL Database Using SSMS Step 1 . Open SSMS and connect to your database

11. How to Restore Database From BAK File SQL Server? Step 2.  Select the database and  right click >> Tasks >> Restore >> Database

12. How to Restore Database From BAK File SQL Server? Step 3.  In the  Restore Database  window, select  From device  under Source for restore section and click the  Browse (…)  button

13. How to Restore Database From BAK File SQL Server? Step 4.   Specify Backup  window will open, set Backup media as  File  and click  Add  button.

14. How to Restore Database From BAK File SQL Server? Step 5.  Select backup file which you want to restore and click OK. Step 6.  The . bak file will be list on the Restore Database window. Click  OK

15. How to Restore Database From BAK File SQL Server? Step 7.  Now, click on  Options  from the left side, select your desired  Restore options  and  Recovery state

16. How to Restore Database From BAK File SQL Server? Step 8.  In the end, click  OK . SQL Database Restore Using T-SQL Restore Full SQL Database Backup RESTORE DATABASE databasename FROM DISK = ' filepath ' GO Ex: RESTORE DATABASE [Ch03_TinyCollege]FROM DISK = 'C:\dev\Ch03_TinyCollege.BAK' Restore Database Backup With NORECOVERY RESTORE DATABASE databasename FROM DISK = ' filepath ' WITH NORECOVERY GO

17. How to Restore Database From BAK File SQL Server? This is the entire process of restoring . bak file. Now, it is clear that how to create a backup and restore database in SQL Server. Sometimes, the SQL backup file is corrupted due to virus attack, sudden system shut-down and many more reasons. In such a case, users unable to restore . bak file in SQL Server because there is no inbuilt utility provided by MS SQL Server to restore damaged SQL backup file. In the next section, we will discuss the solution to  fix corrupted SQL Server Backup file  so that the backup file is easily restored.

18. Solution to Repair Corrupt SQL Server Backup File As mentioned earlier, there is no manual solution to restore. backup file which is corrupted. To repair corrupt SQL . bak file,  SQL Backup Recovery  software is the best solution. This application is smoothly repairing a highly corrupted backup file with their all database objects like tables, columns, triggers, etc. The tool allows you to repair and restore multiple . bak files at once. After recovering corrupt SQL Server backup file, it provides an option to export . bak file to the SQL Server Database.

19. SQL jobs in SQL Server

20. Introduction to the SQL Server Agent In this era of automation, it is often required that we execute multiple scripts repeatedly in a timely fashion. These scripts can be used to back up an existing database, delete extra log files, process data from a table, drop and rebuild indexes on a table, or running an ETL job etc. All these tasks are repetitive and can be automated using the SQL Server Agent. This gives us the flexibility that the job will be executed as per the rules defined in the schedule and there is minimal human intervention required in order to execute these jobs. Once the jobs are executed, you can view the history if the execution was successful or failed. In case of a failure, there is an option to restart the job manually.

21. Introduction to the SQL Server Agent (cont.) There are a few components of the SQL Server Agent service that you must be aware of before proceeding forward . Jobs  – This is a program that defines the rules about the repetitive execution of one or more scripts or other utilities within the SQL Server environment Steps  – These can be considered as the building blocks of the jobs. Your job can contain one or multiple steps. Each step executes a specific set of instructions. The next step can be executed based on the success or failure of a previous step Schedules  – These are periodic rules set on the job to execute automatically based on a pre-defined time slot. These jobs can be scheduled hourly, daily, weekly, monthly, or even on specific days of the week

22. Introduction to the SQL Server Agent (cont.) Alerts  – SQL Server generates events that are stored in the Microsoft Windows Application Log. Whenever the agent finds a match, it fires an alert which is a response to the event Notifications  – You can set up email notifications to update about the result of the job execution. This is mostly done in case of job failures so that the person responsible for the jobs can take appropriate actions.

23. How to find the windows service Now that we have some idea about the SQL Server Agent, let us now go ahead and start the service if not already done. In my machine, the service is currently not running, and I am going to start it from scratch. You can follow the steps below to start the agent service on your machine . Head over to  Run  and type the command  services.msc . Click  OK  once done.

24. How to find the windows service (cont.) Once you hit  OK , the  Services  window will show up. You can scroll below to find the service with the name “ SQL Server Agent (<< INSTANCE NAME >>) ”. As you can see in the figure below, the status of the service is not running. Let us go and start the service.

25. How to find the windows service (cont.) Right-click on the service and select  Start  from the context menu .

26. How to find the windows service (cont.) It might take a while to start the service. Once the service has started, the status will change to  Running .

27. How to find the windows service (cont.) Now, you can verify the status of the SQL Server Agent service using the  SQL Server Management Studio  as well. Head over to SSMS and navigate to the SQL Server Agent on the bottom of the  Object Explorer.

28. Preparing the database Now that our agent service is up and running, let us create our first job using the graphical user interface. We can also create jobs using T-SQL scripts; however, it is out of scope for this article and will be covered in some later articles. To demonstrate the execution of the job, we will create a small table in a SQL Server database that will store the date and time of the execution along with a random number. 

29. Creating the first SQL Server Agent job As we have prepared our database, let us now create the job using the GUI. Right-click on  Jobs  and select  New Job  from the context menu.

30. Creating the first SQL Server Agent job (cont.) As you click on  New Job , the window appears on which you can define the properties of the job that you want to create.

31. Creating the first SQL Server Agent job (cont.) If you look at the figure above, under the  General  tab, I have provided a valid  Name  to the job. The  Owner  of the job should be a defined user in the SQL Server environment. I am logged on using my  Windows Authentication  mode. There is a Description field, where you can provide details regarding the tasks performed by the job. This is purely for documentation purposes. Finally, make sure that the  Enabled  is  checked , otherwise, the job will not trigger. Click  OK  once done and click on  Steps .

32. Creating the first SQL Server Agent job (cont.)

33. Creating the first SQL Server Agent job (cont.) Navigate to the  Steps  section and click on  New . Here we will define the steps in the job. For this article, we will consider a single step, but there can be multiple steps as well.

34. Creating the first SQL Server Agent job (cont.) Provide a  name  to the step and select the step  type . Since we are going to execute a T-SQL statement, please select  Transact-SQL script (T-SQL)  from the dropdown. Select the  Database  on which the script is to be executed and provide the  SQL command  as mentioned. Click  OK  once done. Click on  Schedules  and select  New .

35. Creating the first SQL Server Agent job (cont.)

36. Creating the first SQL Server Agent job (cont.) On the  New Job Schedule  window, provide a  name  for the schedule. Most often, we keep a descriptive name so that it is easier to understand the nature of the schedule. Select the  Schedule Type  as  Recurring  since we are going to execute the job repeatedly. Select the  Frequency  as  Daily  and  Daily Frequency  as  every 10 seconds . In real applications, it is usually scheduled every hour or every day depending on the requirements. Click  OK  once done. Finally, click  OK  on the  Job Properties  page. Your job has been successfully scheduled now.

37. Viewing the data Since the job is scheduled to execute every 10 seconds, we can view the data from the table easily and verify the execution of the job. A new record will be inserted every 10 seconds from now on.

38. Viewing Execution History Alternatively, you can also view the execution history of the job by right-click and selecting View History from the context menu.

39. Viewing Execution History (cont.) This will open the  Log File Viewer  window, where you can see the status of all the executions so far. All successful executions are highlighted in  green  while failed executions will be highlighted in  red .

40. THANK YOU!