Cannot open backup device. Operating system error 5(Access is denied.) while backing up\restoring SQL server Databases.

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

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

Infrastructure: SQL server 2000 and onward

Scenario: This is very famous  error which has been seen no of times in DBA life.

If you go on the error, it says there is some problems with backup device or permission issues and so on. You will get so many blogs and will find proper resolutions referring those blogs, so I am not going to write about that here.

I will simply talk about the scenario I had on my client environment.

Everything was correct  like  Backup command was right,backup media was rightly placed and had full permission and it was verified multiple times.

No problem with media was captured. all were seem to be good but though I was getting Cannot open backup device. Operating system error 5(Access is denied.)

And one thing was quite strange backup command was running fine in new query window in SSMS but it was throwing Cannot open backup device error while automating it through Maintenance Plan. No problem was detected in Maintenance Plan in-fact rest DBs backup were occurring fine.

Resolution:

Since there were no fault in Media, No permission issues, No syntax problems were found.

I run the backup script (GUI then script to new Window) for that particular database through SSMS and suddenly noticed an [EXTRA SPACE] in the end of database name in backup query.

That one extra space was the only culprit and the I checked the database name. I highlighted the DB name in Object explorer and found that DB name also had same extra space that the reason backup command also had same space.

I removed that extra space for that particular database and finally succeeded to take backup automatically.

Hope this helps!

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

 

Only a member of the sysadmin server role can add a job for a different owner with @owner_login_name

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Error: Only a member of the sysadmin server role can add a job for a different owner with @owner_login_name

Infrastructure: SQL server 2005 and onward

Scenario: Here the scenario was, user with DB level access say database owner was trying to create sql job and wanted to change the owner to sa or some other sysadmin account.User also had role in MSDB role as of result she was able to see the jobs and create the jobs but unable to change its owner to sysadmin.

Please refer link for msdb agent role.

 

Resolution:

As per the error, only sysadmin can change the owner to sysadmin sql account or some other account. Once the jobs got created DBA can change the owner and issues disappear but generally application team like to manage their own application related group of jobs without depending on DBA everytime.

In such situation, either DBA needs to create proxy for them through which they can run the job under SQL security context or application team can also do hard-code inside their tsql code to EXECUTE AS.

but all the time EXECUTE AS the code dosent look flexibile and if its SSIS package that needs to run with help of SQL agent job in that case proxy is required.

Please note, t-sql can not be run through proxy account. So in this scenario, proxy can help them out.

Please follow this link to understand and implementing proxy account.

Hope this helps!

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 

 

No global profile is configured. Specify a profile name in the @profile_name parameter

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses. 

Note:  Best viewed in Internet Explorer 6 and above

Error: No global profile is configured. Specify a profile name in the @profile_name parameter

Infrastructure: SQL server 2005 and onward

Scenario: Here the scenario was, client was trying to run some stored procedure. In that stored procedure she was calling send_db_mail to send some query result output as attachment like some excel file but sp was breaking during DB mail shoot.

Resolution:

As per the error it tells us that there is no global profile defined and so we should specify a value for @profile_name but overall error was actually misleading. It was something to do with DB mail profile security.

Follow the below steps to check database public profile security:

  1. Go to DB mail configuration wizard
  2. Manage profile security
  3.  Set public profile security to Yes and increase the MaxFileSize to its max value to allow sending big attachment (this can be set to max 2GB when your query output seems to be big in size)

To change database attachment size, refer below link.

https://www.mssqltips.com/sqlservertip/4599/sql-server-database-mail-attachment-error/

Hope this helps!!

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

 

 

 

 

 

Change database owner for multiple DBs in one Go

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to Change database owner for multiple DBs in one Go

Scenario: Lets say, you want to change DB owner for multiple DBs to say ‘SA’ in case  when the old owner is not active user/account on SQL server.

Infrastructure: The below query/procedure has been run/tested successfully on SQL server 2005,2008,2008 R2 and 2012 resp.

 

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

 

Resolution:

select ‘use ‘ + ‘[‘+ name + +’]’+  ‘;’ +’ ‘+ ‘exec sp_changedbowner ‘+’ ‘+ ”’sa”’ + ‘;’  from sys.databases

where suser_sname(owner_sid)  in (‘ ‘)

You can put the filter clause as per the requirement.

Hope this helps!

PowerShell –SQL Automate Tutorial-1

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Implementation: Basic of PowerShell before diving into SQL automation with the help of PS

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its todays need to learn at least basic of some PS cmdlets.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

Note: Whether PS scripts are getting executed through CMD or On PS window itself we need to first check the Execution policy.

The Default Execution Policy is set to restricted; you can see it by typing:

Get-ExecutionPolicy

If you get restricted policy then change it to RemoteSigned to run the PS command.

Open the PS window (Run as Administrator) for both 32-64 bits and type below command:

For Windows 7, Windows 8, Windows Server 2008 R2 or Windows Server 2012, and so on run the following commands as Administrator:

Set-ExecutionPolicy RemoteSigned

Or you can bypass the execution policy

Example: powershell.exe  -noprofile  -executionpolicy  bypass  -file .\script.ps1

If you do not change to RemoteSigned you will be welcomed with below error message.

Example.ps1 cannot be loaded because the execution of scripts is disabled on this system.

Okay cool!!

Let’s start with code and explanation:

  • Step 1:- Create a simple text file.
  • Step 2:- Write any PowerShell commands

E.g. Get-Process | Sort-Object ID

Note: Get-Process using this you can get a list of processes; “Sort-Object ID” for the list will be sorted by ID. Use pipe symbol for separation “|”

  • Step 3:- Rename extension .txt to .PS1 extension (Create at some location with FirstPowershell.PS1 e.g. E:\Dharmesh\PSTutorial \getprocess.ps1)
  • Step 4:- Open PowerShell and Run E:\Dharmesh\PSTutorial \getprocess.ps1

Output: ID column (object) is sorted in acceding order by default. You can put more than one object (column) in sorting.

Getprocess

Happy Learning!!

 

 

 

Error While Restoring Databases in SQL Server Cluster-Only Formatted Files On Which The Cluster Resource Of The Server Has Dependency Can Be Used

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts
 

Implementation: Restore databases in SQL server fail-over cluster from backup files

Scenario: Today We have been asked to restore few databases from mirrored backup files

The backup files and mirror backup restore syntax were fine and Its two node active passive cluster say AT (Active) and Passive (PS).

Error:

Server: Msg 5184, Level 16, State 2, Line 1. Cannot use file ‘%.*ls’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.

Server: Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

InfrastructureThis steps are from the Windows server 2008 onward cluster .

To add a disk as a dependency to the SQL Server, the shared cluster disk must reside in the same group in the Cluster Administrator as the SQL Server resources.

Resolution:

First make sure the directory where you are restoring (creating) database should be added in SQL Server Group If not follow below instruction…

To move the shared cluster disk, Open Fail-over Cluster manager–>Go to Disk–>select the disk you want to move to the SQL Server group, and then right-click that resource and Change Group. After the disk is in the same group in which the SQL Server resource resides, follow these steps to add it as a SQL Server dependency:

  1. Open the Cluster Administrator (cluadmin).
  2. Make sure that all the physical disk resources that contain SQL Server databases are in the same group as the SQL Server resource.
  3. Right-click the SQL Server resource, and then bring the resource into an Offline state by clicking Bring Offline.
  4. Right-click the SQL Server resource, and then click Properties.
  5. Click the Dependencies tab.
  6. Click Modify to add the disk to the dependencies list for that resource.
  7. Bring the SQL Server resource back online, and then put the SQL Server files on that shared cluster disk
  8. Once this is done, now execute DB restoration or DB creation command.

Hope this helps!

 

 

Common SSPI handshake failed with error code 0x8009030c in SQL errorlog

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

 

Error: SSPI handshake failed with error code 0x8009030c in SQL errorlog

InfrastructureSQL server 2005/2008/2008R2/2012 and so on..

Resolution:

Best explain by Microsoft.Follow the link below.

Common ‘SSPI handshake failed’ errors and troubleshooting


Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts