Backup failed with the following error: "xp_create_subdir() returned error 161

Users might receive below error when executing backup.

Executed as user: NT Service\SQLAgent$SQLTEST. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:50:45 PM Progress: 2015-05-07 13:50:46.22 Source: {2FEF4D33-99C5-4CC6-9ECC-85EDDACC8E9E} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2015-05-07 13:50:46.41 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\SQLbackup\db..." failed with the following error: "xp_create_subdir() returned error 161, 'The specified path is invalid.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error

Error: 2015-05-07 13:50:46.60 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [master] TO DISK = N'\\ SQLbackup \db..." failed with the following error: "Cannot open backup device '\\SQLbackup\SQLTEST\\Full_Backup\master\master_backup_2015_05_07_135046_3323057.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If you see highlighted messages there are two reasons error

1. failed with the following error: "xp_create_subdir() returned error 161

First check SQL server or SQL Server Agent service running using the local or service account and the account should have enough privileges on the backup folder

2. Cannot open backup device. Operating system error 5(Access is denied.)

Check whether the account have read/write privileges on the backup folder.

In my case I see that SQL Server Agent running using a local account and that account don’t have permission on the backup folder. I have changed the SQL Server Agent “Log on As” account to service account which has permission and restarted the service.

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Starting up database message in ERROR.log file

We have noticed that one SQLServer Database is starting very frequently and also it is in "Recovery Pending" mode.

Below error messages from SCOM

Database WEBAPP in SQL Server instance INST1 on computer CLUST.sqlserver-expert.com is offline/recovery pending/suspect/emergency

Below messages from  ERROR.LOG

2013-05-01 12:06:53.29 spid122     Starting up database ‘WEBAPP’.

2013-05-01 12:08:04.20 spid123     Starting up database ‘WEBAPP’.

2013-05-01 12:09:14.25 spid184     Starting up database ‘WEBAPP’.

Solution:- You will see this issue then AUTO CLOSE option is set to TRUE in Database properties.

If you set AUTO_CLOSE to TRUE then the database will be closed as soon as there are no connections to it and database will re-open every time a connection is established to it. 

There is no much information from log. But you will see this error when backup is running, because backup job trying to create a  new connection to the database.

Set AUTO CLOSE option to FALSE to resolve this issue.

Management Studio --> Select the Database "WEBAPP" -->  Right Click  Properties--> Options --> "Auto Close" value to "False"

Regards

Satishbabu Gunukula

http://www.sqlserver-expert.com

Backup SQLServer Database and different types of Backup

You should always have proper backup plan in place to protect your database from failures. SQLServer have four types of backups

1. Full Database backup – Complete database backup at the time of backup.
2. Transaction Log backup – Backups up the transaction log file
3. Differential backup–  Backs up the parts of the database that is changed since the last Full Database backup
4. File and file group backup – Backs up the database files that you specify in the FILE or FILEGROUP option.
In SQLServer either you can mention the backup path or you can create a backup device use the device name during the backup. You can either use T-SQL or SQLServer Management Studio to backup SQLServer Database.

Full Database backup: You can backup whole database, this includes part of transaction log which is needed to recover the database using full backup.

BACKUP DATABASE DB_Name
    TO Backup_Device
    WITH FORMAT;
GO

or
BACKUP DATABASE DB_Name
   TO DISK = ‘D:\SQLBackup\FullBackup\DB_Name.bak’
GO

Transaction Log backup:  You must backup the transaction log, if SQLServer database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full. Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.

BACKUP LOG DB_Name
   TO Backup_Device
GO

or

BACKUP LOG DB_Name
   TO DISK = ‘D:\SQLBackup\LogBackup\DB_Name.trn’
GO

If transaction log is full users will receive error “Log files are running out of space”

Differential backup:- The database must have full back up in order to take a differential backup, it only backups the changes since last full backup.

BACKUP DATABASE DB_Name
  TO Backup_Device WITH DIFFERENTIAL
GO

or

BACKUP DATABASE DB_Name
  TO DISK = ‘D:\SQLBackup\Differ\DB_Name.bak’ WITH DIFFERENTIAL
GO

Full/Differential/Transaction Log  Backup using SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential" or” Transaction Logl”
• Select the appropriate Backup Destination and click "OK"

File and file group backup:- By default each database has PRIMARY file group which is tied to one data file. You can create additional filegroups and add data files to filegroup. You can perform both FILE and FILEGROUP backups.

BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO Backup_Device
GO

or
BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO DISK = ‘D:\SQLBackup\Filegroup\DB_Name.bak’
GO

File or File Groups Backup using SQL Server Management Studio• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential"
• Select Backup component as "Files and filegroups"
• Select the appropriate filegroup
• Select the appropriate Backup Destination and click "OK"

Note that from SQLServer 2008 Enterprise edition and later supports backup compression.
Restrictions on Backup Operation1. You cannot backup  offline Database
2. You cannot run two backups concurrently

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Enable xp_cmdshell in SQLServer

“xp_cmdshell” is a stored procedure and this will allow to run operating system commands from SQLServer.

You can run below command to get a listing of the backup files under “d:\backup\*.bak”

xp_cmdshell 'dir d:\backup\*.bak '
or
execute xp_cmdshell 'dir d:\backup\*.bak '

You will get below error if xp_cmdshell is not enabled, by default xp_cmdshell is turned off.  

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

The xp_cmdshell can be enabled and disabled by executing “sp_configure” or using the Policy-Based Management.

-- Run below command to allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- Run below command to update the currently configured value for advanced options.
RECONFIGURE
GO

-- Run below command to enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- Run below command to update the currently configured value for this feature.
RECONFIGURE
GO

You should see below output results when you execute the commands

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

Users may receive below backup failure errors when xp_cmshell is disabled.

2012-12-07 06:00:17.03 spid50      Error: 18204, Severity: 16, State: 1.
2012-12-05 06:00:17.03 spid60      BackupDiskFile::CreateMedia: Backup device '\\SQL backup\MSSQL01\Full_Backup\AppCenter\AppCenter_backup_2012_12_07_060001_8233143.trn' failed to create. Operating system error 1330(Logon failure: the specified account password has expired.).
2012-12-07 06:00:17.03 Backup      Error: 3041, Severity: 16, State: 1.
2012-12-07 06:00:17.03 Backup      BACKUP failed to complete the command BACKUP LOG AppCenter. Check the backup application log for detailed messages.

If users are taking database backup over network drive, they may still encounter backup failures after enabling xp_cmshell .

When SQLServer is unable to connect or open connection with shared resource, you still see the backup failures. To resolve the issue create a job using below syntax in SQLServer and schedule the job, it will help to resolve connection with shared resource.

Syntax
 xp_cmdshell 'net use  \\SQLbackup\MSSQL01\ password /User:domain\userid'

Net use command - Connects / disconnects the computer from a shared resource like network drive, mapped drivers.

Refer below link for more information on xp_cmdshell
http://msdn.microsoft.com/en-us/library/aa260689(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Unable to start execution of step 1 (reason: line(1): Syntax error)

You may receive the error “Unable to start execution of step 1 (reason: line(1): Syntax error” under below circumstances in SQL Server 2005/2008.
1. Created a new maintenance plan
2. Modified the path in existing maintenance plan
3. Existing database fails due after File servers IP Change..etc

Here is the complete error message:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job ‘ Log_Backup’ failed. (SqlManagerUI)

Additional information from Log File Viewer:-
Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\\\backup\\t..." failed with the following error:"Cannot open backup device \\backup\testdb.trn'. Operating system error 53(The network path was not found.). BACKUP LOG is terminating abnormally.” Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Solution:
1. Check the login used to schedule the backup has access to backup device path and read/write permissions.

Run below t-sql from New Query to check the permission.
EXECUTE master.dbo.xp_create_subdir N'\\backup \testdb'

2. If your Backup Maintenance plan suddenly stopped working after Network maintenance then it may be related to name resolution to File Server Name or IP Change or Alias name that you are using in the backup.

Check whether you are able to resolve correct name, IP address using nslookup or check with your Windows team to fix name resolution.
For ex:- nslookup "server name or IP address or File server name"

3. If you still have issues
- Go to SQL Server Agent\Jobs and click properties
- Go to Steps section
- Highlight the Sub plan and click Edit
- In the “ package section” add a Backslash to the beginning of the path listed

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com