Attach Database Failed

Sometime you may get below error while attaching a database:

Attach database failed for Server 'SERVERNAME\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)

Unable to open the physical file "filename.mdf". Operating system error 5: "5(error not found)".(Microsoft SQL Server, Error: 5120)

To solve above error, make sure that the SQL Server Service account has modify permissions over the physical file on the hard drive that you are trying to attach to a SQL Server instance, and modify permissions also over the folder that contains the file.

Query to get details of permissions on Database objects

Use below query to get details of permissions on Database objects

SELECT ulogin.name AS [User Name],
CASE princ.type WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' WHEN 'S' THEN 'SQL User' END AS [User Type],
princ.name AS [Database User Name], perm.permission_name AS [Permission Type], perm.state_desc AS [Permission State],
CASE perm.class WHEN 1 THEN obj.type_desc ELSE perm.[class_desc] END AS [Object Type],
CASE perm.class WHEN 1 THEN OBJECT_NAME(perm.major_id) WHEN 3 THEN schem.[name] WHEN 4 THEN imp.[name] END AS [Object Name],
col.name AS [Column Name]
FROM sys.database_principals princ LEFT JOIN sys.server_principals ulogin on princ.sid = ulogin.sid LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id  LEFT JOIN sys.database_principals imp ON imp.principal_id = perm.major_id LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id  LEFT JOIN sys.schemas schem ON schem.schema_id = perm.major_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE princ.[type] IN ('S','U','G') AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

Log backup to replace maintenance plan

Use below script to restore maintenance plan from log backup:

USE [Master]
GO
DECLARE @BackupExtention nVarChar(4); SET @BackupExtention = '.trn'
DECLARE @DB nVarChar(128); SET @DB = ''
DECLARE @BackupDir nVarChar(138);

--loop through each databASe in full recovery mode
WHILE @DB < ( SELECT MAX(Name) FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' )
BEGIN
SELECT @DB = MIN (Name)  FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' AND Name > @DB
DECLARE @DatabaseName nVarChar(128); SET @DatabaseName = @DB --SET @DatabaseName = 'TFSWarehouse'
DECLARE @DateTimestamp nVarChar(14); SET @DateTimestamp = '_' + CONVERT(VarChar, GetDate(), 112) + '_' + replace(CONVERT(VarChar, GetDate(), 108),':','')
DECLARE @RemoteBackupPath nVarChar(260);
SET @BackupDir = N'D:\Backup\' + @DatabaseName
SET @RemoteBackupPath = @BackupDir + N'\' + @DatabaseName + @DateTimestamp + @BackupExtention
DECLARE @BackupDevice nVarChar(128); SET @BackupDevice = N'local_' + @DatabaseName + @BackupExtention
--SELECT @DatabaseName, @BackupDevice, @RemoteBackupPath
--drop AND recreate the backup device
IF Exists(SELECT NULL FROM sys.Backup_Devices WHERE Name = @BackupDevice)
EXEC Master..sp_DropDevice @LogicalName = @BackupDevice
EXEC Master.dbo.sp_AddumpDevice @DevType = N'disk', @LogicalName = @BackupDevice, @PhysicalName = @RemoteBackupPath
--create the subdirectory if not already preset
EXECUTE Master.dbo.xp_Create_SubDir @BackupDir
--execute the backup
BACKUP LOG @DatabaseName TO  DISK = @RemoteBackupPath WITH NOFORMAT, NOINIT,  Name = @BackupDevice, SKIP, REWIND, NOUNLOAD,  STATS = 10
END
GO

Unable to open DTS package in SQL 2008 Management Studio

Sometime when we try to import a SQL Server 2000 DTS package into SQL Server 2008 R2 using SSMS, the following error appears:

Managing DTS packages in SQL Server Management Studio requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, on your SQL Server 2008 R2 installation media, locate and run the Windows Installer package for SQL Server 2005 Backward Compatibility (SQLServer2005_BC*.msi). You can also download the DTS runtime from the SQL Server Feature Pack page on the Microsoft Download Center. (Microsoft.SqlServer.DtsObjectExplorerUI)

As suggested, the “Microsoft SQL Server 2005 Backward compatibility” package must be installed. It can be found on this location: http://www.microsoft.com/en-us/download/details.aspx?id=3522

After the installation of “Microsoft SQL Server 2005 Backward compatibility” package the DTS package is imported successfully. Now when we try to open the DTS package, the following error appears:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

To solve this error the “Sql Server 2000 DTS Designer Components” package must be installed. It can be found on this location: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11988

After the installation try to open again the DTS package in SSMS.   

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

If the same error appears, copy the DLL and RLL files as described in below Microsoft link: http://msdn.microsoft.com/en-us/library/ms143755.aspx

To ensure that the DTS designer can be used in SQL Server Management Studio

1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.

2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id% folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder.

For example, for U.S. English, the lang_id subfolder will be "1033".
To ensure that the DTS designer can be used in Business Intelligence Development Studio

1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder.

2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be "1033".