Unable to Restore SQL Server 2000 Database Backup to SQL Server 2012 Instance

SQLServerF1

Although SQL Server 2000 is out of support, still there are some legacy applications still using SQL Server 2000. As there are many latest versions of SQL Server available in the market including SQL Server 2012 and SQL Server 2014 being the latest.

User may think that they can migrate directly from SQL Server 2000 to either SQL Server 2012 or SQL Server 2014, but it is not possible. We cannot even restore a database backup performed on SQL Server 2000 on to an instance running SQL Server 2012 or SQL Server 2014.

Below is the error which you will receive if you try to restore SQL Server 2000 backup file to SQL Server 2012.

Restore of database ‘DBName’ failed . (miscrosoft.sqlserver.management.relationalenginetasks)
The database was backed up on a server running version 8.00.0194. That version is incompatible with this server, which is running version 11.00.2100.

So, in order to resolve this issue, you will first need to restore this database on SQL Server 2005 or SQL Server 2008 R2 SQL Server instance and then perform backup of this database from the new instance and restore it on the SQL Server 2014 instance.

This error “The database was backed up on a server running version **.**.****” can also occur if you are trying to restore a database which was backed up on hither version and then trying to restore on the lower version of SQL Server instance. Example, this error can also occur if you try to restore a database which was backed up on SQL Server 2008 R2 and then you try to restore it on SQL Server 2008(non R2) instance.

This is applicable on below versions of SQL Server

SQL Server 2000
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Maintenance Plan Cleanup Task is Not Deleting the Old Backup Files

SQLServerF1

It is common in most environments to have maintenance plans used to perform regular maintenance tasks like Full, Differential and Log Backups, Rebuild Indexes, Statistics Update and Integrity Check.

Backups are considered one of the most important task and proper care needs to be taken while creating the maintenance plans for performing the database backups in SQL Server. There are many options available while creating the backup maintenance plan and need to choose options correctly to avoid any failures or problems in future.

Although there can be various problems related to configuring backup maintenance plans, one of the small but difficult one to identify for many DBA’s seem to that they enable the task “Maintenance Cleanup Task” to delete old backups older than 3 days or so and they later identify the old backups are not getting deleted. The backup job will run successfully and does not report any errors in this case, the only way DBA’s identify the issue mostly is when the disk drive where backups are stored gets full or has grown to large size and alerts are raised.

Coming to the solution of this problem is mostly due to incorrect values provided to the “Maintenance Cleanup Task”. In the maintenance cleanup task, verify the option “File extension” and see if the extension of the backup to delete is correct, for example if backup task is creating the backups with extension .bak, make sure “bak” is mentioned, if for transaction log backups, extension is .trn, then make sure that the option provided is “trn”. It is important to note to use “bak”(without quotes) instead of “.bak” as using .bak will not delete the old backup files. So, make sure there is no .(Dot) infront of the extension bak or trn.

This is applicable on below versions of SQL Server.

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Common SQL Server Backup Failure Errors and Issues

You can find here some of the frequent or common errors faced which manually performing a database backup or an automated maintenance plan or custom backup job failed.

Backup Error: 3041, Severity: 16, State: 1
Backup Error: 3041, Severity: 16, State: 1.
Backup BACKUP failed to complete the command BACKUP DATABASE database_name. Check the backup application log for detailed messages.

Backup Error 3041 is a generic error which is returned when a backup fails. Along with this error, there are additional errors returned and can be found in the SQL Server errorlog. Look for other specific backup errors, which will point the cause of the backup failure.

Below are more specific errors which cause the backup failures.

Error: 18204, Severity: 16, State: 1
Error: 18204, Severity: 16, State: 1
BackupDiskFile::CreateMedia: Backup device '\\BackupServer\Backups\Test\Test_Backup.bak' failed to create. Operating system error 53(the network path was not found.)

If you carefully read the complete error message, it gives the cause of the failure “Operating system error 53(the network path was not found”.

– Test If you are able to browse to this backup share \\BackupServer\Backups\Test. You need to test using the same account which is performing the backup, meaning if backups are running from SQL Server job, use the SQL Server and Agent service accounts to test access to the backup share.
– If this is happening intermittently, then possibly it was is network glitch, so work with your network administrator to run a network trace to identify network issues.

Msg 3201, Level 16, State 1, Line 1
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Backups\Test_backup.bak’. Operating system error 3(The system cannot find the path specified.).

– Make sure that the path D:\Backups exists, sometimes with user oversight, the path mentioned could be wrong lie the folder name could be backup and we use backups and will result in error. – Another issue when this can occur is, if there are any space at the end of the folder name.

Msg 3201, Level 16, State 1, Line 1
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Backups\Adv.bak'. Operating system error 5(Access is denied.).

– The above error occurred, because of permission issues on the backup folder. Grant Full Control on backup folder to SQL Server service account and to the account with which are you are trying to perform the backups.
– Also, make sure that the account used to perform backup has sufficient permissions to perform backups in SQL Server.

Error: 3043, Severity: 16, State: 1
BACKUP 'Test_DB' detected an error on page (1:12534) in file ’Test_DB.mdf'.
Error: 3043, Severity: 16, State: 1.

– The error 3043 occurs, if there is some sort of corruption in the database, which would again be mostly because if underlying hardware issues.
– Troubleshoot this issue in the direction, by first troubleshooting and fixing the database corruption issue.

Error: 18210, Severity: 16, State: 1
Error: 18210, Severity: 16, State: 1.
BackupMedium::ReportIoError: write failure on backup device '0a158c7d-a7a3-4d5a-8b58-124602e40a14'.
Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

– Error 18210 occurs mostly when a third party backup tool is performing the backups of SQL databases using native T-SQL commands or by backing up the .mdf and .ldf files. Third party tools generally use VDI/VSS and use VSSWriter or SQLWriter for performing the backups. Check for any problems with these writers.
– If you are not using any third party tool for performing SQL database backups, then you can try disabling the OS and SQL VSSwriter to see if backup completes successfully after disabling them.
– Check for any known issues with SQL Server or OS.
– Check for any known issues with the third backup software.
– Refer below for more information on VSS and VDI backups in SQL Server.
SHEDDING LIGHT on VSS & VDI Backups in SQL Server

Msg 3009, Level 16, State 1, Line 1
Msg 3009, Level 16, State 1, Line 1
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.

– Whenever SQL Server database backup is performed, an entry for that backup is made into msdb..backupset table. Check if the data and log files of msdb is set for autogrowth. Verify if there is sufficient free space of the disk drive where msdb files are located.

Msg 3033, Level 16, State 0, Line 1
Msg 3033, Level 16, State 0, Line 1
BACKUP DATABASE cannot be used on a database opened in emergency mode.

– This error can occur if you try to perform backup of a database which is in emergency mode, which is not supported. Bring your database online and then perform the backups.

Msg 4208, Level 16, State 1, Line 1
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

– This error can occur, if we try to perform transaction log backup of a database whose recovery model is set to “SIMPLE”. Transaction Log backups are not supported for databases in Simple recovery mode. If the database is critical and recovering as much data is required and if point in time recovery is important, then change the recovery model of the database to “FULL” and then schedule job to perform regular transaction log backups.

SQL Server Backups causing operating system returned error 1450
The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

– Error 1450 is not related to SQL Server, it is related to Hardware/Operating System, so check for any configuration issues and make changes accordingly.
– Remove /3 GB switch if enabled, and try performing the backups.
– Make sure there is no corruption with database.
– Check for any issues with disk subsystem.

Msg 3241, Level 16, State 7, Line 1 and Msg 3013, Level 16, State 1, Line 1
Msg 3241, Level 16, State 7, Line 1
The media family on device 'PathToBackupFile\BackupFile.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This issue can occur for various reasons, some of them are as mentioned below.

– Make sure the backup file is located on the same server where you are trying to restore. Check if the backup file is not corrupted. Try to move the backup file on to another drive or to another server and see if that works. Run below command to make sure there is no corruption with the backup file.

Restore verifyonly from disk = 'PathToBackupFile\BackupFile.bak'

– This error can also occur if you are trying to restore a database from lower version to higher version. Example, if you are trying to perform a restore from SQL Server 2012 database on SQL Server 2008 R2 instance. This scenario is not supported and cannot do it, so you have to script our all tables/SP’s/Views/Other objects from SQL 2012 and create them on SQL Server 2008 and then export the data from SQL Server 2012 to SQL Server 2008 R2.

Share any backup errors not covered above and we will add it so that it will be helpful for others.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Retrying and Running a Failed Job After Some Delay in SQL Server

There can be scenarios in certain where there can be requirement of automatically rerunning a failed job after a certain amount of delay. For example, a job which fails due to intermittent issue like a network glitch or some file currently been locked by other process, so it would require that the job need to be attempted to be run again after some time, by which there are possibilities of the issue getting resolved by then and our job will completely successfully and no manual intervention is required by a DBA.

There are multiple ways of making a job to run again itself after sometime and you can choose any approach which you prefer, after testing the solution on Test server.

Method 1: – There is an option available in SQL Server Agent Job Step properties, where we can specify, if that particular job step need to be retried if failed and how many times it should retry to run the failed job and how much delay should be there between each retry of the failed step.

You can configure this from SQL Server Agent -> Jobs -> Job Properties -> Steps Tab -> Edit the Step -> Click on Advanced tab -> Set appropriate value for “Retry Attempts” and “Retry Interval(minutes)”

Job_Step_Retry_Options

Job_Step_Retry_Options

Method 2: – This is applicable for jobs which run T-SQL code as part of job step. We can add multiple schedules to a job and Job will run on both the schedules. Modify the job, “so that it first check if the previous run of this job was failed and was it during the first scheduled time, if Yes, then run the T-SQL code again. If it was successful in its last run and time it was run was during its first schedule, then do not do anything. If it was successful in its last run and time it was run was during its second schedule, then run the T-SQL code”. This needs writing this custom logic and of-course need lot of testing and also is limited only if job consists of T-SQL steps.

Method 3 – Create another job which will monitor if out critical Job completed successfully or not, if it failed, then just reruns that particular job. This is much simpler to implement compared to method 2 and also works for any type of job steps like T-SQL or Maintenance plan related jobs, etc.

Below is a sample code which can be used in the new job which monitors other jobs and rerun them if they failed. This requires you to specify the name of the jobs which need to rerun.

/*

This Job step finds if any of the job with names as "Backup_Full" 
and "App_Job" failed in its previous run and If they have failed in the 
current day, this will rerun that job. Change jobs names as per your names 
of jobs in your environment.

*/
 
DECLARE
  @Value [varchar](2048),
  @JobName [varchar](2048),
  @CurrentDate [datetime],
  @Year [varchar](4),
  @Month [varchar](2),
  @MonthPre [varchar](2),
  @Day [varchar](2),
  @DayPre [varchar](2),
  @FinalDate [int]
 
-- Initialize Variables
SET @CurrentDate = GETDATE()
SET @Year = DATEPART(yyyy, @CurrentDate)
 
SELECT @MonthPre = CONVERT([varchar](2), DATEPART(mm, @CurrentDate)) 
SELECT @Month = RIGHT(CONVERT([varchar], (@MonthPre + 1000000000)), 2) 
SELECT @DayPre = CONVERT([varchar](2), DATEPART(dd, @CurrentDate)) 
SELECT @Day = RIGHT(CONVERT([varchar], (@DayPre + 1000000000)), 2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS [int]) 

--Find failed jobs "Backup_Full" and "App_Job"
DECLARE FailedJobs CURSOR FOR
SELECT DISTINCT 
  j.[name]
FROM 
  [msdb].[dbo].[sysjobhistory] h INNER JOIN
  [msdb].[dbo].[sysjobs] j ON h.[job_id] = j.[job_id] INNER JOIN 
  [msdb].[dbo].[sysjobsteps] s ON j.[job_id] = s.[job_id] AND h.[step_id] = s.[step_id]
WHERE 
  h.[run_status] = 0 AND 
  h.[run_date] = @FinalDate AND 
  j.name IN ('Backup_Full', 'App_Job')
 
OPEN FailedJobs

FETCH NEXT FROM FailedJobs
INTO @JobName

WHILE @@FETCH_STATUS=0
BEGIN

-- Restarts the job
  EXECUTE msdb..sp_start_job @JobName

FETCH NEXT FROM FailedJobs into @JobName
END

CLOSE FailedJobs
DEALLOCATE FailedJobs

There may be other methods as well, but mentioned methods are practical and simple and should serve the purpose.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Backup and Restore Frequently Asked Question and Answers (FAQ) in Interviews Part 1

Backups in SQL Server are considered as one of the most important thing which need to be performed regularly to get our data back in case of unexpected issues or a disaster. As a DBA it is very important to be very familiar with the Backup and Restore concepts. Almost in every SQL DBA interview, there will be questions on Backup and Restore.

Below are some of the top SQL Server Backup and Restore Frequently Asked Interview Question and Answers.

What are different types of Backups in SQL Server?
In all versions of SQL Server, below are the type of backups that can be performed.

SQL Server FULL Database Backups – A Full database backup contains the whole database at the time the backup finished. These backups contains all the data in the databases and active portion of the transaction log file too.

SQL Server Differential Backups – Differential Backups contains the data extents which has been changed after a Full Backup.

SQL Server Transaction Log Backup – Transaction Log backups can only be performed on databases whose recovery model is set to either FULL or BULK-LOGGED. Transaction log backups consists of log records inside a transaction log file(.ldf), since last log backup. This allows a databases to be recovered to a point in time, incase of a failure.

SQL Server File Backups – File Backups in SQL Server allows to backup one or more important database files, rather than backing up entire database. There are various rules in place on how you can perform a File backup and how to restore a file backups.

SQL Server Filegroup Backups – Filegroup Backups in SQL Server allows us to backup one or more FILEGROUPS, which contains one or more database files. By default each database has one FileGroup which is PRIMARY filegroup and we can create additional filegroups and add one or more data files to it. If there are Read_Only filegroups, then one can just backup any Read_Write Filegroups instead of backing up the entire database.

SQL Server PARTIAL backups – PARTIAL backups in SQL Server was introduced with SQL Server 2005 which allows us to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files. This is a good option when there are Read-Only filegroups in the database and we do not want to backup the entire database all of the time. PARTIAL backups can be performed for only a Full or Differential backup, but cannot be used with Transaction Log backups.

SQL Server Copy-Only backups – Copy-Only backups are special purpose backups which do not disturb regular backup sequence. Usually performing different types of backup affect the way the database is restored, if you do not want a certain backup to change or disturb a restore sequence, then a Copy-Only backup needs to be performed. A copy-only backup cannot be used as a differential base later. Copy-Only can be used with FULL, Differential and Transaction Log backups.

What are Recovery Models in SQL Server?
Recovery Models are part of database options and determines the allowed possible restore sequence. Recovery model basically dictate SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models as mentioned below.

Full Recovery Model in SQL Server – SQL Server FULL recovery is very commonly used recovery model allows SQL Server to store all transactions data in the transaction log until a transaction log backup occurs or the transaction log is truncated. Full recovery model is the most complete recovery model which allows us to recover all of the data to any point in time, as long as all backups are available.

SIMPLE recovery model in SQL Server – SIMPLE recovery model is the most basic recovery model in SQL Server where every transaction is still written to the transaction log, but once the transaction is committed and the data has been written to the data file the space used for that transaction can be reused for new transactions. As the log file is getting reused, we cannot perform log backups and thus cannot recover data to point in time. We can only restore an FULL database in case of disaster and causes loss of data. This recovery model is used for databases which are not critical and where data loss is acceptable.

Bulk-Logged Recovery Model in SQL Server – In Bulk-Logged Recovery Model all transactions are logged similar to FULL recovery model except for transactions which involve bulk operations like BULK INSERT, CREATE INDEX, SELECT INTO, etc. These operations are not fully logged in the transaction log and therefore do not take as much space in the transaction log, but does not allow point-in-time recovery of database during the time when there are bulk transactions. This recovery model is used if there are regular bulk operations which happen on a database.

How to plan a backup strategy in SQL Server?
There are various types of backup options available in SQL Server and as a DBA we need to plan what backups need to be performed and how frequently those backups need to be performed.

The planning for database backups change based on server to server, or can be different in different environments depending upon Size of database, importance of data, amount of allowed data loss and available storage to store the backups.

Example1:
If the database is small in size and is not very critical and data loss of 24 hours is acceptable, then below would be the backup plan

– Set the database to SIMPLE recover model.
– Perform daily FULL Backups to local disk during the night.
– Copy the daily backups from local disk to Backup Server or Tape.
– At all times, store atleast 3 days of FULL backups on local disk and 1 month of Full backups on Tape.

Example2:
If the database is large in size(100 GB+) and is very critical and data loss of 10 minutes is only acceptable, then below would be the backup plan

– Set the database to FULL recover model.
– Perform weekly FULL Backups to local disk in the night.
– Perform Daily Differential Backup once in a day, except for the day when we are performing the FULL backup.
– Perform Transaction Log backup every 10 minutes.
– Copy the weekly backups from local disk to Backup Server or Tape.
– Copy the daily differential backups from local disk to Backup Server or Tape.
– Copy the transaction log backups from local disk to Backup Server or Tape.
– At all times, store atleast 1 FULL backup on local disk and 1 month of Full backups on Tape.
– At all times, store atleast recent 3 differential backups on local disk and 1 month of differential backups on Tape.
– At all times, store atleast 3 days worth of transaction log backups on local disk and 1 month of transaction log backups on Tape.

How do you recover a database which was crashed or corrupted using database backups?
This is most important questions and is asked almost in every interview. Failing to answer this question may reduce your chances of getting the job.

Normally, you will be give a backup scenario and will be asked on how you will you recover the database using the available backups. Below is the scenario.

Scenario: There is a backup strategy in place where you have weekly FULL database backup which is performed every Sunday at 12:00 AM. There are daily Differential backup which is performed at 12:00 AM, except on Sundays. There are transaction log backups which are performed every 1 hour. Database crashed on Tuesday at 06:10 AM, now you need to recover as much data as possible, how will you recover the data?

Answer: First, we need to restore FULL database backup which was performed on recent Sunday at 12:00 AM. This needs to be restored with NoRecovery option. Next restore latest differential backup which was performed on Tuesday at 12:00 AM with NoRecovery option. Restore all transaction log backups in sequence taken from Tuesday 12:00 AM till 05:00 AM with NoRecovery option and finally restore the last transaction log file taken at 06:00 AM with Recovery option. This should now bring the database online with all the data till Tuesday 06:00 AM. There will be 10 minutes worth loss of data which was stored between 06:00 AM to 06:10 AM.

How will database recovery model impact database backups?
Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE, DIFFERENTIAL and FULL.

– When a database is set to SIMPLE recovery model, only FULL and DIFFERENTIAL backups are allowed. Transaction Log backups cannot be performed.
– Under BULK-LOGGED or FULL recovery mode, all types of backups are allowed.

Can we perform a SQL Server compressed backup?
Starting SQL Server 2008, we can perform a compressed backups. All types of backups can be performed with compressed option. Prior to SQL Server 2008, backups may need to be compressed using third party tools and but need to regularly test to make sure that the backups are be uncompressed and restored successfully.

How can you automate performing database backups in SQL Server?
We can created maintenance plans and use backup tasks option and schedule the maintenance plan to perform backups automatically. We can also create SQL Server jobs and specify the backups commands or create a Stored procedure to perform backups of databases on certain conditions and create jobs and add job step to to run the stored procedure and then schedule the job. Also, there are many third party tools available to automate performing database backup and restores.

 

Configuring SQL Server 2012 Logshipping on AlwaysON Availability Group Databases with Successful Failover of AG and Logshipping

Configuring SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG) is supported in SQL Server 2012 or higher. We can failover AlwaysON Availability Groups between Primary and Secondary Server and also move or enable the Logshipping from new Primary Server to DR server. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02.

Now we can setup Logshipping from Server01 to Server03 just the same way how we setup logshipping between any two server databases as mentioned below.

How to Setup Logshipping between two servers?
– Connect to SQL Server instance from SSMS using instance name as Server01.
– On Server01, right click the database you want to use as your primary database in the log shipping configuration, and go to its Properties and go to last tab “Transaction Log Shipping”.
– Select the “Enable this as a primary database in a log shipping configuration” check box, which will now enable the logshipping configuration options to choose backup, copy and restore settings.
– Under “Transaction log backups”, click on Backup Settings.
– In the Network path to the backup folder box, type the network path to the backup share to use it for placing the transaction log backups.
– If this backup share is located on the primary server itself then type the local path to the backup folder in the “If the backup folder is located on the primary server” text box. If the backup folder is not on the primary server, you can leave this box empty.
– Make sure that the SQL Server service account on the primary server has full permissions on the backup share.
– Configure the Delete files older than and Alert, if no backup occurs within parameters.
– You can choose to customize the schedule for log backup and alerts.
– Starting SQL Server 2008, backup compression is supported, so you may choose to enabled it or set it to use Instance wide backup compression configuration.
– Now, under Secondary server instances and databases, click Add.
– Click on Connect and then connect to the SQL Server instance Server03 that we want to use as the secondary server for logshipping.
– In the Secondary Database box, either choose a database from the list or just type the name of the database you want to create.
– On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
– Make sure that you choose correct option above and appropriately prepare the database on secondary server.
– Now go to Copy Files tab, and in the “Destination folder for copied files” text box, put the path of the folder to which the transaction logs backups should get copied. This folder is often located on the secondary server or common share. Make sure that SQL Server service account on secondary has full permissions to this path.
– You may optionally choose to change the copy and alert thresholds as per your SLA’s
– Now move to Restore tab, under “Database state when restoring backups”, choose the No recovery mode or Standby mode option. If you are choosing Standby mode option, then also select that you want to disconnect users from the secondary database while the restore operation is performed.
– If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
– You may optionally alter the restore and alert thresholds.
– If you want, you may also add a third server as Monitor server instance to monitor this log shipping configuration. You need to decide whether you want to use monitor server or not, because, if you want to add the monitor server later, we need to remove logshipping entirely and reconfigure it with monitor server.
– If you choose to configure monitor server as well, then click on Connect and connect to the SQL Server instance to use as the logshipping monitor server.
– Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server. Under History retention, choose the length of time you want to retain a history of log shipping.
– On the Database Properties dialog box, logshipping tab, click OK to begin the configuration process.
– Verify and make sure that logshipping log backup job is running successfully on Primary server Server01 and is creating the backup file on the backup share we provided to place the log backup files.
– Verify and make sure that logshipping copy job is running successfully on Secondary Server Server03 and is copying the backup file from the backup share to path specified during copy settings for secondary server.
– Verify and make sure that logshipping restore job is running successfully on Secondary Server Server03 and is restoring the backup files.
– Check Logshipping report from SSMS on Server01 and Server03, by right clicking on SQL Server Instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

Now we have successfully setup logshipping from Server01 to Server03, Next thing is to failover AlwaysON Availability Group “PROD_LSTNR” from Server01 to Server02, after which we also want to move the logshipping to happen from Server02 to Server03 and disable logshipping from Server01 to Server03.

Disable the logshipping from Server01 to Server03 by following below steps
Connect to SQL Server instance using SSMS with instance name as Server01 and Server03
Disable logshipping log backup job on Server01.
Disable logshipping copy job on Server03 related to Server01 as primary.
Disable logshipping restore job on Server03 related to Server01 as primary.

Perform AlwaysON Availability Group “PROD_LSTNR” failover from Server01 to Server02. Verify to make sure databases are synchronized and no errors related to AlwaysON.

Now configure logshipping from Server02 to Server03
– Connect to SSMS using SQL Server instance name as Server02.
– Configure logshipping from Server02 to Server03 using steps mentioned above under section “How to Setup Logshipping between two servers?”.
– Make sure logshipping is working fine by checking new backup job on Server02 and new copy and restore jobs on Server03 which are related to Server02 as primary.

From now onwards, you can failover AlwaysON Availability Group from Server01 to Server02 or vice-versa and at same time switch the logshipping to be performed from Primary AlwaysON AG database to Server03, by disabling the logshipping jobs related to AlwaysON AG secondary server.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 

SQL Server 2012 Logshipping Backup Job Completes Successfully but No Backup File Created

I encountered a strange issue while working on SQL Server 2012 AlwayaON Availability Group databases which also has Logshipping configured. After failover of AlwaysON Availability Group databases from primary to secondary, and also after moving the logshipping from new primary server to DR server, logshipping backup job was having issues where the job was completing successfully, but was not creating the log backup files. Below is the environment setup.

Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02. Logshipping has been setup from Server01 to Server03(This is just same way how we setup logshipping between any two server databases). AlwaysON and Logshipping has been working without any problems after the initial setup.

Now, when there was a planned maintenance to replace Server01 and Server02 with new hardware, we can failover AlwaysON AG to secondary server and also configure or move logshipping from new primary server to DR server. We decided to do this with minimum downtime to production users by moving all databases to one of the server and to perform the maintenance on the AlwaysoN AG secondary server.

As part of planned maintenance, we failed over everything to Server01 and performed maintenance on Server02 and added it back to the AlwaysON AG. Verified and AlwaysON AG databases got synchronized in some time and logshipping also was working fine without any problems.

Now we have to perform maintenance on Server01, so we moved the AlwaysON AG “Prod_LSTNR” to Server02, which was successful and databases are synchronized as well. As expected, there is no logshipping configuration on databases now as logshipping was only setup when databases were primary on Server01 to Server03. We decided to configure logshipping from Server02 to Server03 now, so that logshipping to Server03 happens when the AlwaysON AG databases are either on Server01 or Server02.

Disabled Logshipping Backup job on Server01, disabled logshipping copy and restore jobs on Server03, which disabled logshipping from Server01 to Server03. Took server01 down for maintenance and is no more available now. Configured logshipping from Server02 to Server03 which created new backup job on Server02 and new copy and restore jobs on Server03. All logshipping jobs started running without any errors.

Later, maintenance of Server01 finished and was online and got connected to AlwaysON AG “Prod_LSTNR” and successfully synchronized as secondary.

After some time, we started receiving alerts for logshipping that the copy and restore latency was above the thresholds. We checked and found backup, copy and restore jobs from Server02 to Server03 are all running successfully. Logshipping report shows that there was no backup, copy or restore performed since Server01 was back online. Checked the history of backup job and noticed that backup was not performing any backups, it was just skipping performing the log backup on Server02.

Started verifying the logshipping and AlwaysON settings and noticed that AlwaysON AG backup preferences was set to run backups on any replica with priority of 60 for Server01 and priority of 40 for Server02. As now, we have both Server01 and Server02 online and Server02 acting as AlwaysON Primary, log backups cannot be performed on Server02 due to low priority and that Server01 is online. Changed the AlwaysON AG backup preferences to run backups on Primary Server, after which backup job started creating the backup files successfully. Later copy and restore jobs also starting working without any issues or latency.

You can find AlwaysON backup preferences from SQL Server management Studion (SSMS) -> Expand Always On High Availability -> Availability Groups -> Open properties of Availability Group where the databases in part of -> Go to Backup preferences tab, where we can change the settings.

If we see the issue only from logshipping perspective, we could not have identified the issue, looking into the issue as AlwaysON along with logshipping helped us in identifying the issue.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Unable to perform Backup or Restore to Cluster Shared Volume(CSV) Using SSMS GUI in SQL Server 2014

I had been playing with new features of SQL Server 2014 and I encountered an issue with SQL Server 2014 Cluster Shared Volume(CSV) related to Backup/Restore. Refer, if you are new to Cluster Shared Volume(CSV) whose supported has been introduced starting SQL Server 2014. This is a Windows feature which has been available since Windows Server 2012, but SQL Server only started supporting from SQL Server 2014.

I have setup two node Windows Server 2012 cluster and installed one SQL Server 2014 clustered instance. I have added a disk as Cluster Shared Volume(CSV).

When I try to backup a database, I encountered an issue where I tried to perform backup of a database and normally we see file structure where we can browse to choose the location where to backup the databases, but I see a blank file structure, so nothing there to select any path. If I try to specify the backup path manually to CSV disk, I get an error as below. as mentioned in the error, I have checked permissions to the CSV path, but no issues with the permission. I am able to create databases on CSV and access them, but just could not use the CSV path to perform backups/restores.

C:\ClusterStorage\MSSQL\MSSQL12.InstanceName\Backups

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

After some research, I found out couple of workarounds to perform backups or restores.

Alternative 1 – Use T-SQL for performing backup from SSMS Query Analyzer

Alternative 2 – Follow below mentioned steps

– Add a regular cluster disk(Non Clustered Shared Volume(CSV)) to SQL Server Group in the cluster from Cluster Administrator and then add the new disk resource as dependency to SQL Server Group.

– From SSMS, right click on the SQL Instance properties, go to database settings tab, copy the existing path and save it for future reference and then change the path to the new regular disk which we just added, this is just like how we used to be in SQL Server 2012 or previous versions.

– Now try to perform the backup and you will be able to open the locate backup file and locate database files dialog with out any errors. We can also specify the backup path manually to CSV, but the path still will not be visible from the GUI to browse.

– Later, the new regular storage which we added before could be removed and then change back the default backup location to CSV path which we saved before changing to to new disk in Instance properties -> Database Settings tab.

– You should not encounter any more issues and now you can also see CSV path from GUI to choose path to backup/restore.

There is a Connect bug opened for this issue where we cannot see CSV path using SSMS GUI while trying to perform backup/resstore. You may check on the connect page to see any updates on fixing this issue in coming Service Pack/Cumulative Updates.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 

Unable to Open Logshipping BackJob Properties for Database with AlwaysON AG Created

Recently while working on a production deployment, faced an issue with SQL Server LogShipping. As, it was planned maintenance, we had to find and fix the issue immediately. Writing this blog, so that others can get an idea on what can be done when faced with this type of issue.

Coming to the issue, we had three SQL Server 2012 servers Server01, Server02 and Server03. AlwaysON Availability Group had been setup between Server01 and Server02. Logshipping is setup between Server 01 and Server 03.

We had a planned maintenance where the two servers Server 01 & Server02, hardware was planned to be replaced, so we planned in a way that we will take down one server at a time and perform the maintenance, while the other server will be serving production traffic.

So, we failed-over the AlwaysoN AG with listener name as “Prod_RPT_LSTNR” from Server01 to Server02 which was successful. We disabled Logshipping backup job on Server01, disabled copy and restore jobs. on Server03. Configured logshipping from Server02 to Server03, which completed successfully, new backup, copy and restore jobs started working fine. Now Server01 was taken down for maintenance.

Later, logshipping started failing with errors related to backup path being incorrect. We checked and found that drive where we pointed backup path was removed and we were told that the drive cannot be added back, so we had to change the backup path in logshipping.

We tried to change the path and tried to click on the Logshipping backup job button in logshipping configuration tab, but it was returning below error


TITLE: Microsoft SQL Server Management Studio
------------------------------
SQL Server Management Studio could not save the configuration of 'Server01' as a Primary.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An entry for primary server Server01, primary database ReportServerTempDB does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)
--------------------------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

– After some troubleshooting and research, we found that, when we configured logshipping from Server02 to Server03, we connected to the SQL instance Server02 from SSMS via AlwaysON AG listener name which was Prod_RPT_LSTNR, but later when we tried to change the backup settings we connected to SQL instance Server02 SSMS using server name which was Server02. Now, we connected back again to SQL instance Server02 via SSMS using Listener name, then it allowed us to click on logshipping backup job button and we were able to open the job properties with out any errors.

Running below command on Secondary server, will provide us with the details of logshipping primary database like primary_server, primary_database, backup_source_directory, backup_destination_directory, file_retention_period, etc.

sp_help_log_shipping_secondary_primary
@primary_server = 'SQLInstanceName',
@primary_database = 'DatabaseName'

Running below command on Primary server will provide details of primary database settings

sp_help_log_shipping_primary_database
@database = 'DatabaseName'

Now, important thing to understand here is that, when we configure logshipping in environments where we have multiple ways of connecting a SQL instance, example via AlwaysON Listener name, IP Address, Alias, etc, we need to connect using the same method later as well, if we want to make any changes to the logshipping configuration. This happens because, the SQL Server instance name which we used to connect will be stored in MSDB logshipping metadata and will look for same server name later, so if later we try to connect using another method by specifying different instance name like IP Address/listener/alias, it will mis-match with the metadata and will return errors.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 

Error while Editing the backup maintenance plan in SQL Server 2005

Recently while I was trying to edit a maintenance plan, when trying to edit the backup task using Management Studio resulted in an error. Below is the error message


TITLE: Microsoft SQL Server Management Studio
Cannot show the editor for this task.
Value of ’27/6/2014 12:00:00 AM’ is not valid for ‘Value’. ‘Value’ should be between ‘MinDate’ and ‘MaxDate’.

After some research, found that this has impacted other users as well and a connect bug Link has been filed for the same.

There was no fix released for this, but there is a work around available which resolved the issue and then I was able to edit the backup task in the maintenance plan.

Below is the work around

1. Modify Maintenance Plan.
2. Click On the backup task.
3. Click F4 button on the keyboard and you will find the properties window to the right corner of the SSMS.
4. Change the value in “expiredate” by adding 10 years or Clear out the value in “expiredate”

Backup Task Maintenance Plan Properties

Backup Task Maintenance Plan Properties


5. Save the maintenance plan.
6. Reopen the maintenance plan and now you should be able to edit the backup task.

Happy Reading,
SQLServerF1 Team

 
1 2