Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked, with error similar to the below,

“Cannot open backup device 'D... The package execution fa... The step failed.,00:00:01,0,0,,,,0”



Detailed Error Message
==================

Executed as user: MachineName\SYSTEM. ...9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:15:03 AM Progress: 2008-07-24 07:15:04.11 Source: {134957B2-5C5F-4D4F-BDB7-ECAC9C3D8E20} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-07-24 07:15:04.69 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Program F".: 100% complete End Progress Error: 2008-07-24 07:15:04.71 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [Mstest ] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak' WITH NOFORMAT, NOINIT, NAME = N'MStest _backup_20080724071504', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Cannot open backup device 'D... The package execution fa... The step failed.



Analysis
=======

When the “Create a sub-directory for each database” option is selected, the Maintenance task executes the xp_create_subdir procedure to create a directory with the Database Name. If you carefully look at the Detailed Error message above, you will see that there is a space character in the Database name.
“D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MStest \Mstest _backup_200807240715.bak”


If you try to run the Backup command extracted from the above error in the Query windows it gives a more informative error ,

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.



Cause:
======
The Operating System does not allow you to create a folder with trailing spaces. When xp_create_subdir creates the folder with the space, the OS creates the folder but without the trailing space.

For example you can try this command EXECUTE master.dbo.xp_create_subdir N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \'

Now when the backup command fires it fails to read the directory, since there is no directory with a space created.


Resolution
=========
Follow the below method to remove the trailing space from the Database Name

1.
Right click and rename the database to any name. (This is because Management Studio will not allow you to remove the trailing space, since it thinks there is no change made to the name and that a database already exist with the same name
2.
Now rename the database to the original name without the space.
3.
Reconfigure your Maintenance Plan

Failed to notify ''operator name'' via email?...

First Check the value for Database mail profile is NUll

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile'

If So

select 'exec master.dbo.xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'', N''DatabaseMailProfile'', N''REG_SZ'', N''' + name + ''''
from msdb.dbo.sysmail_profile
where profile_id in (Select profile_id from msdb.dbo.sysmail_principalprofile )

Copy the output and Execute, it will be as below

exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA'

Now Restart the SQL Agent. mail will work now.


------------------------------ OR --------------------------------

Verify below Steps are configured properly or not

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

Configuring above steps will work make DB mail to work properly.

Connecting to a Remote Integration Services Server

To connect to Integration Services on a Remote Server

1.
Open SQL Server Management Studio.

2.
Select File, Connect Object Explorer to display the Connect to Server dialog box.

3.
Select Integration Services in the Server type list.

4.
Type the name of a SQL Server Integration Services server in the Server name text box.




Note
The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.


5.
Click Connect.







Eliminating the "Access Is Denied" Error

--------------------------------------------------------------------------------



When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

To configure rights for remote users on Windows Server 2003 or Windows XP

1.
If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3.
Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4.
Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5.
Right-click on MsDtsServer and select Properties.

6.
In the MsDtsServer Properties dialog box, select the Security tab.

7.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8.
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9.
Click OK to close the dialog box.

10.
Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

11.
Close the MMC snap-in.

12.
Restart the Integration Services service.


To configure rights for remote users on Windows 2000 with the latest service packs

1.
Run dcomcnfg.exe at the command prompt.

2.
On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.

3.
Select the Security page.

4.
Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.

5.
Close the dialog boxes and dcomcnfg.exe.

6.
Restart the Integration Services service.




Connecting by using a Local Account

--------------------------------------------------------------------------------



If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.



Delegation Is Not Supported

--------------------------------------------------------------------------------



SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.








Thanks to :- http://msdn.microsoft.com/en-us/library/aa337083.aspx

Backup Log Database with truncate _only

It became deprecated Command and removed in 2008. You can achieve the same result by sending the file to the NUL blackhole

BACKUP LOG [DBNAME] TO DISK='NUL'
GO
DBCC SHRINKFILE("LOGFILE",0)


OR

alter database DBNAME set recovery simple
go
alter database DBNAME set recovery full
go
sp_helpdb 'DBNAME '
GO




use DBNAME
go
dbcc shrinkfile(LOGFILE,0)

Cluster node 'SQLSVRHOST00' was removed from the active failover cluster membership. The Cluster service on this node may have stopped.

= Event ID 1135 — Cluster Service Startup =
===========================================
http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx

Event Details

Product: Windows Operating System
ID: 1135
Source: Microsoft-Windows-FailoverClustering
Version: 6.1
Symbolic Name: EVENT_NODE_DOWN
Message: Cluster node '%1' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.


Resolve

Check network hardware and configuration

If you do not currently have Event Viewer open, see "Opening Event Viewer and viewing events related to failover clustering." After reviewing event messages, choose actions that apply to your situation:
Run the Validate a Configuration Wizard, selecting only the network and inventory tests. For more information, see "Using the Validate a Configuration Wizard to review the network configuration."
Check the system event log for hardware or software errors related to the network adapters on this node.
Check the network adapter, cables, and network configuration for the networks that connect the nodes.
Check hubs, switches, or bridges in the networks that connect the nodes.

To perform the following procedures, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.

Using the Validate a Configuration Wizard to review the network configuration

To use the Validate a Configuration Wizard to review the network configuration:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, in the console tree, make sure Failover Cluster Management is selected. Then under Management, click Validate a Configuration.
3.Follow the instructions in the wizard to specify the cluster you want to test.
4.On the Testing Options page, select Run only tests I select.
5.On the Test Selection page, clear all check boxes except those for the Network tests.
6.Follow the instructions in the wizard to run the tests.
7.On the Summary page, click View Report.

Opening Event Viewer and viewing events related to failover clustering

To open Event Viewer and view events related to failover clustering:
1.If Server Manager is not already open, click Start, click Administrative Tools, and then click Server Manager. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the console tree, expand Diagnostics, expand Event Viewer, expand Windows Logs, and then click System.
3.To filter the events so that only events with a Source of FailoverClustering are shown, in the Actions pane, click Filter Current Log. On the Filter tab, in the Event sources box, select FailoverClustering. Select other options as appropriate, and then click OK.
4.To sort the displayed events by date and time, in the center pane, click the Date and Time column heading.

Verify

To perform this procedure, you must be a member of the local Administrators group on each clustered server, and the account you use must be a domain account, or you must have been delegated the equivalent authority.

Verifying that the Cluster service is started on all the nodes in a failover cluster

To verify that the Cluster service is started on all the nodes in a failover cluster:
1.To open the failover cluster snap-in, click Start, click Administrative Tools, and then click Failover Cluster Management. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
2.In the Failover Cluster Management snap-in, if the cluster you want to manage is not displayed, in the console tree, right-click Failover Cluster Management, click Manage a Cluster, and then select or specify the cluster that you want.
3.If the console tree is collapsed, expand the tree under the cluster you want to manage, and then click Nodes.
4.View the status for each node. If a node is Up, the Cluster service is started on that node.

Another way to check whether the Cluster service is started is to run a command on a node in the cluster.

Using a command to check whether the Cluster service is started on a node

To use a command to check whether the Cluster service is started on a node:
1.On the node that you are checking, click Start, point to All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.
2.Type:
CLUSTER NODE /STATUS

If the node status is Up, the Cluster service is started on that node.

Tail-log Backup without the Primary Data File

While I was reading a post, written by Vinod Kumar (blog | twitter) about Files and FileGroups with SQL Server, I found an interesting quiz question: “Do we need the primary data file available to backup your transaction log after a crash?“

The answer to the above question is No, if the transaction-log file is not damaged, then we can initiate a log backup to backup the data till the time of the crash, since last log backup. To support my answer, I have created the following test script. In the first part of the script, I have created a test database named “TestDB” and change its recovery model to “Full”. The recovery model of the model database on my SQL 2008 R2 Express instance is set as Simple, so I manually change the recovery model of TestDB to Full.

use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go

Since I have created the database, its time to initiate a full backup of the database. Next I will add a demo tables with two columns and inserted some data into the table. After verifying the data in the table, initiate the first transaction-log backup.

-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go

Since the first log backup is completed, lets add some additional data into the table in the TestDB database. After inserting the additional row into the table, I will verify the same.

-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/

Now I am going to crash the TestDB database. I am going to shutdown my SQL Server Express instance and manually delete the data file of the TestDB database. Once the file is deleted, restart the SQL Server instance, and try to access data from the database. The following error will occur: “Database ‘TestDB’ cannot be opened due to inaccessible files or insufficient memory or disk space“

-- try to access the table, will get error as data file is missing
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go

As I am unable to access the database, I need to backup the transaction log to retrieve the data, which was inserted into the database after the last transaction-log backup, to minimize the data loss. The syntax to initiate the tail-log backup is as follows:

-- Initiate the tail log backup
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */

If the transaction-log file of the database is not damaged, then the above command will create the log backup (also known as tail-log backup). This completes the answer to the given question; however, a backup is never successful, if it can’t be restored. So I am going to test, whether I can restore the database to the same point, when the crash occurred, using the available backups. The next part of the script will restore the database and verify the data in the database after the restoration is completed.

use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go

The database is recovered successfully using the available backups and the complete data is also recovered. If we place the data file and the t-log files in different drives, in case, if the drive containing the data file is damaged, then we can retrive the data if we are able to backup the t-log. However, if the drive containing the t-log files is crashed, then we are going to face some data loss.

The complete script to reproduce the above scenario is mentioned below:

/* Create Tail-log backup after a crash, if the primary data file is missing
------------------------------------------------------------------------------
@skganguly
*/
use [master]
go
if exists (select 1 from sys.databases where name = 'TestDB')
drop database TestDB;
go
-- Create database TestDB
use [master]
go
create database [TestDB] ON Primary
(name = 'TestDB_Data', filename = 'C:\projects\demodb\TestDB_Data.MDF',
size = 10MB, filegrowth = 10MB)
LOG ON
(name = 'TestDB_Log', filename = 'C:\projects\demodb\TestDB_Log.MDF',
size = 10MB, filegrowth = 10MB);
go
-- Changing the Recovery Model to full
use [master]
alter database [TestDB] set recovery full;
go
-- Initiate a Full backup of the database
use [master]
Backup database [TestDB] to disk = 'C:\projects\TestDB.bak'
go
-- Create a table in the database
use [TestDB]
create table dbo.demotable1 (
demoid int identity(1,1),
demodate datetime default getdate()
);
go
-- Insert base data
use [TestDB]
insert into dbo.demotable1 default values;
go 10
-- verify base data
select * from TestDB.dbo.demotable1;
go
-- Initiate the 1st T-log backup
use [master]
backup log [TestDB] to disk = 'c:\projects\testDB1.trn';
go
-- Insert some additional data
use [TestDB]
insert into dbo.demotable1 default values;
go 15
-- verify all the data in the database
select COUNT(*) from TestDB.dbo.demotable1;
go
/*(total = 25, till log backup it was 10)*/
-- initiate the crash, for this demo, I've shutdown the server and
-- delete the data file manually, to verify whether the tail-log backup is
-- possible or not if the data file is not available
-- try to access the table
use [TestDB]
select COUNT(*) from TestDB.dbo.demotable1;
go
-- Database 'TestDB' cannot be opened due to inaccessible files
-- or insufficient memory or disk space
/* Initiate the tail log backup*/
use [master]
BACKUP log [testDB] to disk = 'C:\Projects\TestDB_Tail_Log.trn'
with No_Truncate, Norecovery;
go
/* This complete the tail log backup without the primary data file */
/* The script will not be successful, if we can't restore the tail log.
Thus, we are going to restore the database and verify the data before cleanup */
use [master]
Restore database [TestDB]
from disk = 'C:\projects\TestDB.bak'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\testDB1.trn'
with replace, norecovery;
go
Restore Log [TestDB]
from disk = 'c:\projects\TestDB_Tail_Log.trn'
with recovery;
go
-- Verify the data
Select count(*) from TestDB.dbo.demotable1;
go
-- clean up the database
use [master]
drop database [TestDB]
go

=======================================
Thanks To :- http://sudeeptaganguly.wordpress.com/2011/04/15/taillogbackupwithoutdatafile/

Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)

Thanks to :- http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/



Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\AdventureWorks.bak’. Operating system error 5(Access is denied.).

Fix/Resolution/Workaround:

I checked which user account my SQL Server services are running.

I gave full permission to that account to get read of the error.

NOTE: I have checked with my personal machine and not on production server. Please check with your network administrator for permission on production server.