Attach db failure cause log not primary

Hi Everybody,

I need URGENT help with this. Because of space problem my collegue create a second log file on another drive. I wanted to clean the thing, made a detach, renamed the ldf into old, but now it won't attach anymore, I get the following messages:

EXEC sp_attach_db @.dbname='TARIFINFO_GEN',
@.filename1 = 'F:\Data\TARIFINFO_GEN_Data.mdf'

Server: Msg 5171 F:\Data\TARIFINFO_GEN_log.ldf is not a primary database file
Server: Msg 1813 Device activation error the physical file name 'F:\Data\TARIFINFO_GEN_log.ldf' may be incorrect

Do someone know what to do or where to look for a solution. Many thanks in advance, Isabelle.Try sp_attach_single_file_db 'TARIFINFO_GEN',
'F:\Data\TARIFINFO_GEN_Data.mdf'

Also, in many instances to clean logs you should be able to run something like:

backup log TARIFINFO_GEN with truncate_only

and then a dbcc shrinkfile.

Instead of detaching and re-attaching.

Eric

Attach database from network drive

Hello,

when I attach an database from a network drive in SQL Express 2005 the access permissions on the data and log files will be change.
This ends in a deadlock when the user doesn't have administrator right on the network server, because the permissions will allow access only for administrators and an unknown Usergroup on the network server. I think it's the SQLServer2005MSSQLUser$xxx$MSSQLSERVER group from the local server.

Example:

DBCC TRACEON(1807)
GO
PRINT 'ATTACH FIRST'
CREATE DATABASE TEST ON
( FILENAME = N'\\server\tmp\test.mdf' ),
( FILENAME = N'\\server\tmp\test_log.ldf' )
FOR ATTACH;
GO
PRINT 'Detach'
exec sp_detach_db TEST
GO

When this batch runs again after some minutes you will get

Msg 5120, Level 16, State 101, Line 2

Die physikalische Datei "\\server\tmp\test.mdf" kann nicht ge?ffnet werden. Betriebssystemfehler 5: "5(Zugriff verweigert)".

This could be translated like:

The physical file "\\server\tmp\test.mdf" could not be opend. OS Error 5 (Access Denied).

This error is the same when the user doesn't have full access on the share and db files.

In SQL2000/MSDE no security permissions will be changed, so the database could always be attached.

Is there any workaround?

Perhaps setting the security permissions could switched off?

You also need full access permissions on the share and files, wich also doesn't make sense.

Hi Dieter (thanks for translating ;-)),

you have to make sure that the SERVICE account which is starting up the SQL Server service has the appropiate permissions on the network drive. Unless this can′be accomplished you can′t attach the database via network.

HTH, Jens Suessmeyer.

|||

Hi Jens,

The Service account has permissions on the network drive.

The problem is that SQL Express need full rights because of changing the permissions when attaching the db files.

First time the database could attached, but then the permissions are changed an the database could not attach again.

Dieter Pelz

Attach a Database from multiple computers? (SQL Server Express 2005)

Can multiple computers,running SQL Server Express 2005, simultaneously
attach to a single database file, hosted on a networked drive? Eg
does one instance of Express lock the database when it is attached?
I am replacing Access db calls in a program and it access a db on a
shared location. I would rather not try to get the client to set up a
network enabled Express server!
"David" <dm_fw@.sbcglobal.net> wrote in message
news:6uo2i2528b3ju7vp1bqbm7hi8d8ft3fe7r@.4ax.com...
> Can multiple computers,running SQL Server Express 2005, simultaneously
> attach to a single database file, hosted on a networked drive?
No. A SQL Server instance requires exclusive access to its database files.
And moreover attaching database files on a network is not supported.

>Eg
> does one instance of Express lock the database when it is attached?
> I am replacing Access db calls in a program and it access a db on a
> shared location. I would rather not try to get the client to set up a
> network enabled Express server!
Well, that's just the way SQL Server works.
David
|||"David" <dm_fw@.sbcglobal.net> wrote in message
news:6uo2i2528b3ju7vp1bqbm7hi8d8ft3fe7r@.4ax.com...
> I am replacing Access db calls in a program and it access a db on a
> shared location. I would rather not try to get the client to set up a
> network enabled Express server!
Then you are using the wrong product. What on earth would be the point of
replacing Jet with just another file sharing program?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Attach a Database from multiple computers? (SQL Server Express 2005)

Can multiple computers,running SQL Server Express 2005, simultaneously
attach to a single database file, hosted on a networked drive? Eg
does one instance of Express lock the database when it is attached?
I am replacing Access db calls in a program and it access a db on a
shared location. I would rather not try to get the client to set up a
network enabled Express server!"David" <dm_fw@.sbcglobal.net> wrote in message
news:6uo2i2528b3ju7vp1bqbm7hi8d8ft3fe7r@.4ax.com...
> Can multiple computers,running SQL Server Express 2005, simultaneously
> attach to a single database file, hosted on a networked drive?
No. A SQL Server instance requires exclusive access to its database files.
And moreover attaching database files on a network is not supported.
>Eg
> does one instance of Express lock the database when it is attached?
> I am replacing Access db calls in a program and it access a db on a
> shared location. I would rather not try to get the client to set up a
> network enabled Express server!
Well, that's just the way SQL Server works.
David|||"David" <dm_fw@.sbcglobal.net> wrote in message
news:6uo2i2528b3ju7vp1bqbm7hi8d8ft3fe7r@.4ax.com...
> I am replacing Access db calls in a program and it access a db on a
> shared location. I would rather not try to get the client to set up a
> network enabled Express server!
Then you are using the wrong product. What on earth would be the point of
replacing Jet with just another file sharing program?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Attach a Database from multiple computers? (SQL Server Express 2005)

Can multiple computers,running SQL Server Express 2005, simultaneously
attach to a single database file, hosted on a networked drive? Eg
does one instance of Express lock the database when it is attached?
I am replacing Access db calls in a program and it access a db on a
shared location. I would rather not try to get the client to set up a
network enabled Express server!"David" <dm_fw@.sbcglobal.net> wrote in message
news:6uo2i2528b3ju7vp1bqbm7hi8d8ft3fe7r@.
4ax.com...
> Can multiple computers,running SQL Server Express 2005, simultaneously
> attach to a single database file, hosted on a networked drive?
No. A SQL Server instance requires exclusive access to its database files.
And moreover attaching database files on a network is not supported.

>Eg
> does one instance of Express lock the database when it is attached?
> I am replacing Access db calls in a program and it access a db on a
> shared location. I would rather not try to get the client to set up a
> network enabled Express server!
Well, that's just the way SQL Server works.
David|||"David" <dm_fw@.sbcglobal.net> wrote in message
news:6uo2i2528b3ju7vp1bqbm7hi8d8ft3fe7r@.
4ax.com...
> I am replacing Access db calls in a program and it access a db on a
> shared location. I would rather not try to get the client to set up a
> network enabled Express server!
Then you are using the wrong product. What on earth would be the point of
replacing Jet with just another file sharing program?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Attaach 2000 data file from network drive

Hi
How can I attach mdf file without copying it to local disk.
I dont have enough space on the server which I have to attach db. But there
is enough space on an other server in the network.
Thanks
BanuYou will need to set this trace flag to enable
dbcc traceon(1807)
Be aware that this isn't entirley supported by Microsoft and any network
delays can lead to corruption. Also performance will suffer...
HTH. Ryan
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
> Hi
> How can I attach mdf file without copying it to local disk.
> I dont have enough space on the server which I have to attach db. But
> there
> is enough space on an other server in the network.
> Thanks
> Banu|||Dear Ryan,
I execute the dbcc traceon(1807) . But still couln't see the mapped network
drive when I try to attach the mdf file.
What else should I do?
"Ryan" wrote:

> You will need to set this trace flag to enable
> dbcc traceon(1807)
> Be aware that this isn't entirley supported by Microsoft and any network
> delays can lead to corruption. Also performance will suffer...
> --
> HTH. Ryan
> "Banu_tr" <abuslu@.hotmail.com> wrote in message
> news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
>
>|||Banu_tr wrote:
> Dear Ryan,
> I execute the dbcc traceon(1807) . But still couln't see the mapped networ
k
> drive when I try to attach the mdf file.
> What else should I do?
> "Ryan" wrote:
Make sure you refer to the network path with the UNC name rather than a
drive letter. Like:
EXEC sp_attach_db 'JUNK',
'\\SERVER\share\MSSQL\data\JUNK.mdf',
'\\SERVER\share\MSSQL\data\JUNK_log.LDF'
or:
EXEC sp_attach_db 'JUNK',
'\\SERVER\C$\MSSQL\data\JUNK.mdf',
'\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
IMO running a database from a network drive is a near suicidal thing to
do if you care about the integrity or availability of your data. Do
this for a READ-ONLY or SINGLE USER database only. Make sure you have a
current backup BEFORE you attempt it. Don't expect decent performance
or reliability.
Read:
http://support.microsoft.com/defaul...kb;en-us;304261
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Dear David,
I still got the message "Device Activation Error"
any advice?
"David Portas" wrote:

> Banu_tr wrote:
> Make sure you refer to the network path with the UNC name rather than a
> drive letter. Like:
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\share\MSSQL\data\JUNK.mdf',
> '\\SERVER\share\MSSQL\data\JUNK_log.LDF'
> or:
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\C$\MSSQL\data\JUNK.mdf',
> '\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
> IMO running a database from a network drive is a near suicidal thing to
> do if you care about the integrity or availability of your data. Do
> this for a READ-ONLY or SINGLE USER database only. Make sure you have a
> current backup BEFORE you attempt it. Don't expect decent performance
> or reliability.
> Read:
> http://support.microsoft.com/defaul...kb;en-us;304261
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Banu_tr wrote:
> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>
Are you sure the server has access to the share you are trying to use?
Same advice as before: don't do it. Not for production use anyway. Tell
your boss, customer or business owner that they need to purchase some
more storage.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Banu_tr wrote:
> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>
You need to check that the account that the SQL Server service runs
under can access the share where your data files are located. If SQL is
running under the service account then you need to change it to run as
a domain login, give that login the necessary permissions, then stop
and restart the SQL server service. Having done that, it should work
but as already indicated this is not a fully supported option and never
having tried it I don't know under what circumstances it might not work
at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

ASPNETDB.MDF I need to rename this file

I need to rename this db and be able to set a path to it. This is because my webhost places all SQL databases on a seperate drive. This is a shared drive with alot of other databases. There fore my ASPNETDB.MDF needs to be renamed and have a path assigned. This doesnt seem tro be taken care of in my web.config file? Can some one help in reasigning the default database name and pathway. my other databases were pretty straight forward.

Thanks

Use this setting in web.config:

<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.mdf;Database=ms;Trusted_Connection=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings
Replace the AttachDbFilename setting with your own.

Jos

|||

I don't believe the autoattach feature with the User Instance attribute is going to work in a hosted web site, as this only works with SQLEXPRESS and only for the local machine. Most likely your hoster is NOT running SQL Express. You can use the Database Publishing Wizard to script the entire database, connect to the one for your account at the hoster, and run your script to recreate your "stuff".

|||

Both these answers are good. I dont seem to have a database publishing wizard in my express editions. I do need to get access to this though as am using scripts atm.

I have created a passwords login page and roles. when i set these in the web administration tool it created a db called ASPNETDB.MDF.

in the public database drive at my webhost there are lots of db's and I have to give my db a unique name. When I try and change the name of this db on my development system express creates a new db with the name ASPNETDB.mdf.

(due to a video i just saw) I think I can solve this issue by running aspnet_regsql but i cant get this program to access ssmse as my development copy of ssmse does not allow remote access.

|||

There are System.Web.Management (I believe that's the namespace) classes that allow you to create code (or a page) that does everything that ASPNET_REGSQL.EXE does.

The Database Publishing Wizard is a free download. Look it up on the web. Cheers.

|||

Thanks to you both you have been a great help.

ASPNETDB with publish provider

Hi!

I have created a sql script with publish provider and the file has all information about ASPNETDB and now i want to drive the sql script into my databas on my webbhosting.

How do i? i haveSQL Server Management Studio Express to admin my databas.

any?

I doubt you can connect directly to the database server on your webhosting company, but if you can then you should do like this:

Start Management Studio Express. Choose connect to server. Enter the name of the server (or IP) you wish to connect to. Make sure you enter the correct username and password. In this case I believe you should be using SQL authentication with the login you've been given from your hosting company. Once logged in, you start a new query (which will basically give you an empty window). Make sure your database is selected in the combo, and then simply paste your SQL code and press "play" :-)

If you cannot connect directly, through Management Studio Express, they may have web interface (such asSQL Server Web Data Administrator)that you can use to login to the server through the browser. But you must check with the company if they have such a system.

If this option is not available either, then you need to wrap up your code in an ASP.net page and execute it on the server. Let me know if this is what you need, and I can help you getting started with this.

Good luck!

|||

I think it works fine, my tables was created in my databases system tables folder.

But when iam trying to login, i get this messages:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'dbname', owner 'dbo'.

DO you iknow anyting of that?

|||

You installed the tables under a database login that "owns" the database i.e. is dbo.

When you log into your application, the connection is using different login/password that is not dbo i.e. has not been permissioned to use the stored procedures within your database. Either use the dbo login to provider your application login with the appropriate permissions (in this case "GRANT EXECUTE on [stored procedure name] to [login name]", or assign your application login with dbo permissions.

If you take the former route of assigning permissions on the actual database objects to your application login - note that the login will probably need execute permissions on all the stored procedures starting with aspnet_ in that database.

|||

So now i have created my table with a correct owner, but when i try to login on my webpage, i get same error.

How can i change the login ASP.NET control to check this table owner.aspnet_Users instead of dbo.aspnet_Users?

Any?

|||

Why do you want the aspnet_ tables under a different owner? They really should be under dbo.

|||

Because i get error messages when i try to login on the webpage, (the error message i write on top).

But if you have another idés so tell me. My userid to the database maybee couldt work with owner dbo.

|||

One Simple Answer to You.

SQL Server Database Publishing Services

http://www.microsoft.com/downloads/details.aspx?FamilyId=6F03273C-FFC8-4F5E-BAFC-041FBD68FD1E&displaylang=en