Feeds:
Posts
Comments

Archive for the ‘Backup & Restore’ Category

Earlier, I have written an article about how to take a backup automatically in place of manual backup. I received very good feedback about this post.

Pre-requisite :

Problem :

The problem is, once automatic backup would be scheduled, it will start piling up the back up in the storage and soon, you will be out of space. In this case, you delete the backup manually or automatically ? I prefer automatically. But how to configure it ?

Solution :
Let me explain the solution step by step :

Step 1 :
It would be the best, if you select the “Maintenance Cleanup Task” in Step 6 in the Pre-requisite article. However if you did not do it there, you can modify the same maintenance plan and do it.
First of all you need to select the same Maintenance plan that you have created in the earlier article and right click on it, in order to modify it, as shown below.

cleanup task.1.1

Step 2 :
Once you select modify, SQL Server Management Studio (SSMS) will take you to the design screen of the maintenance plan. Now you need to select “Maintenance Cleanup Task” from the toolbar available in the left hand and drag and drop it on the right hand design window as shown below.

cleanup task.1.2

Step 3 :
Once you drag and drop the “Maintenance Cleanup Task” in the design window of the maintenance screen on the right hand side, you need to select the backup up task. Once you select it, it will show a green arrow in the bottom. Just drag that green arrow and drop it at the head of “Maintenance Cleanup Task” as shown below. The reason behind to drag and drop the green arrow is, to develop a link between both task. In addition to this, this link will define the sequence as well, so that the backup process will be taken first and then cleanup task, in order to remove the old backup.

cleanup task.1.3

Step 4 :
It seems that configuration is done. However, I can see a red cross sign on “Maintenance Cleanup Task” and the reason behind it that we did not configure the “Maintenance Cleanup Task”.
So lets configure it. In order to configure it, you need to double click on “Maintenance Cleanup Task”, you will get “Maintenance Cleanup Task” property window. Here most of the stuff is self explanatory. However, bear in mind that extension must NOT be with “dot”. Generally it is “bak”, in order to delete backup files ONLY as shown below.

cleanup task.1.4

Step 5 :
Now, you are done with the configuration, just save the maintenance plan and close it. Now it will automatically take the backup and delete the older files defined in step 4.

Let me know, how you clean your older backup files.

Advertisements

Read Full Post »

Last Sunday, my support team received a backup from one of our customers and customer requested some activities for their backup. Once we saved the backup it was named testing backup.  The problem was, we deployed lots of databases for different applications at this client and we did not know that which database backup is he referring to. First of all it is not a best practice to name a backup like this, it must be proper naming conversion. However if you come across situation like this, there is a workaround to find the details about the backup without restoring it. But how ?

Given below is the script that will give you the details about the backup without restoring it to any database.

--This script is compatible with SQL Server 2005 and above.
USE master
GO
RESTORE FILELISTONLY
FROM DISK = N'C:\data\Testing Backup.bak'
GO

metadata of backup.1.1

In addition to the above information, if you need further details about the backup without restoring, you can use RESTORE HEADERONLY & RESTORE LABELONLY

Reference : MSDN

Read Full Post »

‘Can you restore SQL Server 2012 backup in earlier versions of SQL Server?’ I came across this question many times from the community and the simple answer is NO. You cannot restore a new version of SQL Server backup in earlier versions. However, there is a way around that can be followed to achieve it. Basically, you can generate the script of all objects along with the data using SQL Server 2012 with any earlier versions compatibility and run those scripts on earlier versions of SQL Server. This is how you can get the complete data with schema from SQL Server 2012 to earlier versions.

Problem :
Before proceeding with the solution, I would like to show you the error, that happens when you restore a higher versions of
SQL Server database backup to an earlier version of SQL Server. Given below is the image.

erromessage33241.1

The media family on device ‘E:\DBBackup\AdventureWorks2012_Backup\AdventureWorks2012_Backup’ is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

SOLUTION :
Let me proceed with the solution, step by step.

Step 1:
Open SSMS and right click on the database that you want to restore in earlier versions of SQL Server and then select Task and then further click on Generate Script …
Given below is the image.
Restore SQL Server 2012 backup on earlier versions.1.1

Step 2 :
Given below is the informational screen, that will guide you how to generate the scripts along with the steps. Press NEXT button.
Restore SQL Server 2012 backup on earlier versions.1.2

Step 3 :
In this step, you will get two options. Given below are the details.

  1. Script entire database and all database objects
  2. Select specific database objects

Option 1 is preferred because you can generate all database objects altogether from it. However if you upgrade any object using any new features of SQL Server 2012, then you should select Option 2 and exclude that particular object and press NEXT button. Given below is the screen image.

Restore SQL Server 2012 backup on earlier versions.1.3-1

Step 4 :
In this option you need to select the script file location to save all the scripts in a particular file. Rest of the options you can leave as default. Once you are done with the file location, you can see an ADVANCE button. Click on this button. It will open the advance option for the scripting.
Restore SQL Server 2012 backup on earlier versions.1.4

Step 5 :
Once you are inside the Advance option, you need to select further two options. Given below are the details.

  1. Script for SQL Server version
  2. Type of data to script.

In the Option 1 make sure that you have selected the right earlier Version of SQL Server, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.5

In the Option 2, make sure that you have selected Schema and data, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.6-1

Once you configure both options, press OK and press Next in the previous screen, as shown below.

Restore SQL Server 2012 backup on earlier versions.1.7

Step 6
Once you press NEXT button, it will take you to the summary screen where you can see all your configuration altogether.
Again press NEXT button to proceed, as show in the image below.

Restore SQL Server 2012 backup on earlier versions.1.8

Step 7
In this step, SQL Server will automatically script all your selected object along with the data. And will give you the status report, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.9

Restore SQL Server 2012 backup on earlier versions.1.10

Step 8 :
Now you need to browse the file you saved in particular location in the Step 4 and Execute it in selected earlier version of SQL Server.

Let me know if you came across this problem and resolved it in a different manner.

Read Full Post »