August 7, 2009 at 3:01 am
Hi,
First post here, hope you guys can help.
We have a tool that restores a .bak file to an SQL 2005 server, just so it can pull the data and forward this data. Afterwards the restored database is of no use. Every time the tool runs, it restores to the database, replacing whatever data is there.
We are moving the temporary database to a new SQL 2008, something that should be rather easy, as it it just a matter of changing an INI file, and giving the correct rights on the new sql server. the users have the DBCreator role, and when the database is restored, the user currently running the tool will become DB owner, so there should be access to the database to pull the data.
But there isnt.
they receive an error: 18456, Severity 14, State 38 error. which i believe means no access to that database right?
also on the sql logs i can see the database constantly "starting database 'databasename'".
i can see the status of the restored database is 'Shutdown, Normal'. and auto_close is true. Is this what is causing my problem? and if so, why is this not a problem on the sql 2005 server where the auto_close is also True? The reason i believe this might be the cause, is that others have had similar issues due to this auto_close + when setting the auto_close to false, my sql log is no longer filled with the "starting database" event.
Is there a way to force all databases to have the auto_close set to false, no matter what settings the database has in the backup?
by the way, is it not possible for me to change the restore parameters on the tool. 🙁
August 7, 2009 at 12:53 pm
If you are restoring the setting is being applied from the database backup. In reality you shouldn't have autoclose on for any database. You'll have to change the process to run an ALTER DATABASE after the restore to change the setting.
ALTER DATABASE AdventureWorks
SET AUTO_CLOSE OFF
Really the best solution is to change the setting on the source database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 11, 2009 at 2:56 am
While I agree with Jack that you shouldn’t use the option of auto_close for the databases, I don’t think that this is the cause of your problems. When user tries to connect to a database that is closed the database reopens automatically and the user shouldn’t get any error message. In order to find why the login failed, you should make sure that you log failed login in SQL Server’s error log. Then you should look at that error at the error log and check the value for the error’s state. You can use this URL http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx%5B/url%5D to get the reason for the failed login according to the state.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 12, 2009 at 1:06 am
Thank you both for your answers.
i am not able to change backup procedures, or willing to at the moment is more accurate, as this would have to be changed for around 1200 locations running sql server express, where the backup is coming from. and we will be building a new tool from scratch within 6 months, where this will no longer be an issue.
i am no longer certain that this is because of the auto_close flag, as sysadmin rights given to the user, eliminates the issue, which would indicate a rights issue i suppose. what i dont get is, the user has DBcreator rights, and when restoring a backup the user is made DB owner on the restored database. what rights could the user be missing then? unless special rights are required to start the database?
i am getting
Error: 18456, Severity: 14, State: 38. which means that the user doesnt have access to the database it is trying to connect to apparently. Even though the user is DB_owner
Could this also be an issue with sql 2005 express --> sql 2008 ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply