Problems in attaching db

Does anyone know why trying to attach a db (.mdf and .ldf files) to a new db, the "Attach database" item in the menu is disabled, please? do I have to reinstall SQL Server?Originally posted by atorlone
Does anyone know why trying to attach a db (.mdf and .ldf files) to a new db, the "Attach database" item in the menu is disabled, please? do I have to reinstall SQL Server?

You could try to attach your db with a t-sql statement.

sp_attach_db @.dbname = 'db name',
@.filename1 = 'path_to/file.mdf' ,
@.filename2 = 'path_to/file.ldf'|||I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanks!|||Originally posted by atorlone
I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanks!

Do you run MsSql 7 or 2000 ?|||Originally posted by atorlone
I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF',
@.filename2 = N'C:\MSSQL7\Data\AARETAIL_Log.LDF'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\MSSQL7\Data\AARETAIL_Log.LDF' is not a valid database file header. The PageAudit property is incorrect.

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanks!

try attaching the database without the logfile:
EXEC sp_attach_db @.dbname = N'AARETAIL',
@.filename1 = N'C:\MSSQL7\Data\AARETAIL_Data.MDF'

Problems bringing up Project REAL

Most of the installation went well. I got to the point of attaching the

source database, but had problems attching the warehouse database:

Database 'REAL_Warehouse_Sample_V6' cannot be started in this edition

of SQL Server because it contains a partition function 'pf_Range_Fact'.

Only Enterprise edition of SQL Server supports partitioning.

I am running standard edition at mey development workstation, the only

one we have running. I do not necessarily need to see partitioning

working. But sounds like I will need to install Enterprise edition at

my development computer to go down this path of discovery? Anyone been

able to do otherwise?

Is there any way to get in and look at the stored procedures at least, or schemas?

Even after going back, it was not clear to me from the prerequisit list that Enterprise edition was required for this.Developer Edition would work as well. Simply a $50 fee for that.|||Yes, it works!

Edition upgrade sucks. I've done it twice, two different ways, and each time it trashes SQL installation. Both times I ended up doing a complete uninstall, and then install. So really the edition upgrade was a wasted diversion of time.

Problems bringing up Project REAL

Most of the installation went well. I got to the point of attaching the source database, but had problems attching the warehouse database:
Database 'REAL_Warehouse_Sample_V6' cannot be started in this edition of SQL Server because it contains a partition function 'pf_Range_Fact'. Only Enterprise edition of SQL Server supports partitioning.
I am running standard edition at mey development workstation, the only one we have running. I do not necessarily need to see partitioning working. But sounds like I will need to install Enterprise edition at my development computer to go down this path of discovery? Anyone been able to do otherwise?
Is there any way to get in and look at the stored procedures at least, or schemas?
Even after going back, it was not clear to me from the prerequisit list that Enterprise edition was required for this.Developer Edition would work as well. Simply a $50 fee for that.|||Yes, it works!

Edition upgrade sucks. I've done it twice, two different ways, and each time it trashes SQL installation. Both times I ended up doing a complete uninstall, and then install. So really the edition upgrade was a wasted diversion of time.

Problems attaching to database from VWD

Hi Mike,

I hope you can help me with my SQL Server Express problem. I have a database that was created in VWD Express 2005, that is currently in C:\web projects\project name\App data...etc. I have been able to connect to and use the database with an application that I am building as a learning exercise, ( I'm a complete newbie to .NET and SQL Server) but now I can't connect to the database from SQL Server manager.

I have tried attaching the file which has worked a few times, but now that won't work either. I keep getting a message that the file can't be found or I do not have sufficient permissions to access it. I can connnect to the file fine from within VWD, but because of the above problem I can't now run the application (it throws an exception).

I suspect that I've got some of the basic configuration settings wrong, but I really don't know where to start looking. Below is an example of one of the messages I've been getting

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
CREATE FILE encountered operating system error 32(error not found) while attempting to open or create the physical file 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf'. (.Net SqlClient Data Provider)

Any help would be very gratefully recieved

regards

jon

Hi Jon,

I split this into a new thread, you added it to the bottom of an unrelated question where it would have gotten lost.

The first problem I see is that you're said your file was located at C:\web projects\project name\App data, but the error says it's looking for the file at 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf', these are two different directories. I'm not sure where the difference comes from.

The real problem is probably related to User Instances. When you create a database in VWD, it's going to set it up to use a User Instance, which is a special process of SQL Server that is started just for the application calling your database. Since a database file can only be attached to a single instance of SQL Server at a time, you can't attach the database to the parent instance, which is what you are looking at in Management Studio, while it is being used by the User Instance that is started by your application.

The second likely issue is that SQL Express is running as Network Service, which is a low privelelge account that does not have permissions to the directory where your database is located. SQL Express can not attach to database files in folders that it doesn't have permissions for. You would need to set the folder permissions to allow tje Network Service account access to the database file.

For the record, the way the VS Express products, such as VWD Express, are designed to work with SQL Express pretty much assumes that you will do all your database manipulation using the VWD interface. Trying to use Managment Studio in combination with VWD Express can cause some odd behavior because of this. You should read the white paper on User Instances to gain a better understanding about what this is as it is what's going to cause you the most confusion if you try to use these two products together.

Mike

|||

Thank you very much for your help with this issue, I will read the White paper that you suggested and hopefully that will clear a few things up. I didn't realise that VWD and VS Express work so independantly of each other, and you are right, the behavior has been odd and inconsistent, which added to my confusion.

Thanks again for your help,

Jon

|||Hello Mike,

> The second likely issue is that SQL Express is running as Network Service, which is a low privelelge
> account that does not have permissions to the directory where your database is located. SQL Express
> can not attach to database files in folders that it doesn't have permissions for. You would need to set
> the folder permissions to allow tje Network Service account access to the database file.

have found this:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx
See headline Detaching and Attaching a Database.

I have written a little visual basic 2005 test program and found the following.

There is a SQL Server login for the standard user which I used mapped to the sysadmin server role.

1.
The .mdf file is in a subdirectory of the all users profile directory.
Network service has no permissions on the .mdf file:

Windows authentication:

Logged on as administrator:
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

Logged on as standard user:
The standard user himself did copy the file in the directory.
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file read only.

Logged on as standard user.
The standard user himseld did copy the file in the directory.
sp_attach_db attaches the .mdf file read only.

2.
The .mdf file is in the Data Directory of SQL Server.
Network service has full permissions on the .mdf file:

Windows authentication:

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
sp_attach_db ends with the message Access denied.

So it seems that a standard user can't run a program which calls an attach.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
There is a login for the user mapped to the sysadmin server role.
sp_attach_db attaches the .mdf file correctly.

Here this worked what did not with Windows Authentication.

|||Supplement:

To Point 1.
After attaching a .mdf file which is in i a subdirectory of the all users profile directory:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

After detaching the Database with the Management Studio i tried again:

Windows authentication:
Logged on as standard user:
Access denied.

SQL Server server authentication:
(SA was the user in the connection string.)
Logged on as standard user.
sp_attach_db attaches the .mdf correctly.

|||

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop (name of database)
EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.

Problems attaching to database from VWD

Hi Mike,

I hope you can help me with my SQL Server Express problem. I have a database that was created in VWD Express 2005, that is currently in C:\web projects\project name\App data...etc. I have been able to connect to and use the database with an application that I am building as a learning exercise, ( I'm a complete newbie to .NET and SQL Server) but now I can't connect to the database from SQL Server manager.

I have tried attaching the file which has worked a few times, but now that won't work either. I keep getting a message that the file can't be found or I do not have sufficient permissions to access it. I can connnect to the file fine from within VWD, but because of the above problem I can't now run the application (it throws an exception).

I suspect that I've got some of the basic configuration settings wrong, but I really don't know where to start looking. Below is an example of one of the messages I've been getting

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
CREATE FILE encountered operating system error 32(error not found) while attempting to open or create the physical file 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf'. (.Net SqlClient Data Provider)

Any help would be very gratefully recieved

regards

jon

Hi Jon,

I split this into a new thread, you added it to the bottom of an unrelated question where it would have gotten lost.

The first problem I see is that you're said your file was located at C:\web projects\project name\App data, but the error says it's looking for the file at 'C:\Inetpub\wwwroot\BalloonShop\App_Data\BalloonShop.mdf', these are two different directories. I'm not sure where the difference comes from.

The real problem is probably related to User Instances. When you create a database in VWD, it's going to set it up to use a User Instance, which is a special process of SQL Server that is started just for the application calling your database. Since a database file can only be attached to a single instance of SQL Server at a time, you can't attach the database to the parent instance, which is what you are looking at in Management Studio, while it is being used by the User Instance that is started by your application.

The second likely issue is that SQL Express is running as Network Service, which is a low privelelge account that does not have permissions to the directory where your database is located. SQL Express can not attach to database files in folders that it doesn't have permissions for. You would need to set the folder permissions to allow tje Network Service account access to the database file.

For the record, the way the VS Express products, such as VWD Express, are designed to work with SQL Express pretty much assumes that you will do all your database manipulation using the VWD interface. Trying to use Managment Studio in combination with VWD Express can cause some odd behavior because of this. You should read the white paper on User Instances to gain a better understanding about what this is as it is what's going to cause you the most confusion if you try to use these two products together.

Mike

|||

Thank you very much for your help with this issue, I will read the White paper that you suggested and hopefully that will clear a few things up. I didn't realise that VWD and VS Express work so independantly of each other, and you are right, the behavior has been odd and inconsistent, which added to my confusion.

Thanks again for your help,

Jon

|||Hello Mike,

> The second likely issue is that SQL Express is running as Network Service, which is a low privelelge
> account that does not have permissions to the directory where your database is located. SQL Express
> can not attach to database files in folders that it doesn't have permissions for. You would need to set
> the folder permissions to allow tje Network Service account access to the database file.

have found this:

http://msdn2.microsoft.com/en-us/library/ms189128.aspx
See headline Detaching and Attaching a Database.

I have written a little visual basic 2005 test program and found the following.

There is a SQL Server login for the standard user which I used mapped to the sysadmin server role.

1.
The .mdf file is in a subdirectory of the all users profile directory.
Network service has no permissions on the .mdf file:

Windows authentication:

Logged on as administrator:
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

Logged on as standard user:
The standard user himself did copy the file in the directory.
sp_attach_db attaches the .mdf file correctly.
After this attach the file permissions have changed:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file read only.

Logged on as standard user.
The standard user himseld did copy the file in the directory.
sp_attach_db attaches the .mdf file read only.

2.
The .mdf file is in the Data Directory of SQL Server.
Network service has full permissions on the .mdf file:

Windows authentication:

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
sp_attach_db ends with the message Access denied.

So it seems that a standard user can't run a program which calls an attach.

SQL Server server authentication:
(SA was the user in the connection string.)

Logged on as administrator.
sp_attach_db attaches the .mdf file correctly.

Logged on as standard user.
There is a login for the user mapped to the sysadmin server role.
sp_attach_db attaches the .mdf file correctly.

Here this worked what did not with Windows Authentication.

|||Supplement:

To Point 1.
After attaching a .mdf file which is in i a subdirectory of the all users profile directory:
Administrator, Network Service and the SQL Server user group where Network Service is in have full permissions. All other permissions where deleted.

After detaching the Database with the Management Studio i tried again:

Windows authentication:
Logged on as standard user:
Access denied.

SQL Server server authentication:
(SA was the user in the connection string.)
Logged on as standard user.
sp_attach_db attaches the .mdf correctly.

|||

Hi Mike,

I've had a look at the white paper on use instances and also stopped using Management Studio, as you suggested. The problem is that I can run my application from VWD quite happily, until for no reason the application can't connect to the database. This happens ramdomly and is very frustrating. I have worked out a way to correct this problem with the following method (I don't know if this is correct but it's the only work around I know.

1) I close down VWD and open Management Studio,

2) I right click to attach a database, and then navigate to the .mdf file for the database, and click to attach it. I then delete the existing icon for that database (which I can't acess anyway, it only shows me the main icon but not the tables)

3) I rename the new instance of the database to the correct name (minus path info etc)

4) I create an SQL script as follows:

EXEC sp_grantlogin 'jon\ASPNET'

(This line executes correctly),

Then I enter these lines:

USE BalloonShop (name of database)
EXEC sp_grantdbaccess 'jon\ASPNET'

(At this stage when I execute the query Management Studio informs me that this name already exists in the database and so doesn't execute)

5) I now close out of Management Studio and re-open VWD, and I can now run my application correctly.

I know I've got some configuration settings wrong somewhere but I find the whole user instance thing confusing. For the record I'm running the appllication through VWD's built in web server, I did originally have it through IIS but I moved it (this will explain the confusion of locations from my first post, I gave you the original location, sorry about that).

Any advice you can give would be gratefully recieved, ie is there an easy way to reconfigure my settings to make everything work as expected or have I got to start the database again from scratch (not a pleasent thought). Also is there an idiots guide to SQL Server Express anywhere that I can read.

Jon

ps I've just realised that I get the 'can't connect to database problem when running the application after I've made a change to the database from VWD ie adding a new stored proceedure, Don't know if this helps, but better you get the full picture.

Problems attaching a SQL 2000 database

I have a customer that is trying to attach a SQL 2000
database into SQL Server 2000. I have tried Enterprise
Manager (Attach Db), T-SQL "sp_attach_db "
and "sp_attach_single_file_db" without success. I keep
on getting a errors like the ones shown in this snippet
from the SQLERRORLOG:
2003-12-10 10:00:58.79 spid51 SQL Server Assertion:
File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
2003-12-10 10:00:58.79 spid51 Error: 3624, Severity:
20, State: 1.
2003-12-10 10:00:58.79 spid51 Error: 3313, Severity:
21, State: 2
2003-12-10 10:00:58.79 spid51 Error while redoing
logged operation in database 'MYDATABASE'. Error at log
record ID (84983:2649:335)..
2003-12-10 10:00:59.27 spid51 Error: 9004, Severity:
23, State: 7
The client does not have a backup :o(. Does anyone know
how I can recover from this situation? A repair option
would be much appreciated.
Thanks,
BobIt is time to call MS PSS... Sorry
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <bob.albert@.quest.com> wrote in message
news:088401c3bffe$1ebd59b0$a001280a@.phx.gbl...
> I have a customer that is trying to attach a SQL 2000
> database into SQL Server 2000. I have tried Enterprise
> Manager (Attach Db), T-SQL "sp_attach_db "
> and "sp_attach_single_file_db" without success. I keep
> on getting a errors like the ones shown in this snippet
> from the SQLERRORLOG:
> 2003-12-10 10:00:58.79 spid51 SQL Server Assertion:
> File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
> line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=> MAXDATAROW'.
> 2003-12-10 10:00:58.79 spid51 Error: 3624, Severity:
> 20, State: 1.
> 2003-12-10 10:00:58.79 spid51 Error: 3313, Severity:
> 21, State: 2
> 2003-12-10 10:00:58.79 spid51 Error while redoing
> logged operation in database 'MYDATABASE'. Error at log
> record ID (84983:2649:335)..
> 2003-12-10 10:00:59.27 spid51 Error: 9004, Severity:
> 23, State: 7
> The client does not have a backup :o(. Does anyone know
> how I can recover from this situation? A repair option
> would be much appreciated.
> Thanks,
> Bob|||Thanks Wayne,
I guess I'll have to do just that!
Bob
>--Original Message--
>It is time to call MS PSS... Sorry
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Bob" <bob.albert@.quest.com> wrote in message
>news:088401c3bffe$1ebd59b0$a001280a@.phx.gbl...
>> I have a customer that is trying to attach a SQL 2000
>> database into SQL Server 2000. I have tried Enterprise
>> Manager (Attach Db), T-SQL "sp_attach_db "
>> and "sp_attach_single_file_db" without success. I keep
>> on getting a errors like the ones shown in this snippet
>> from the SQLERRORLOG:
>> 2003-12-10 10:00:58.79 spid51 SQL Server Assertion:
>> File: <p:\sql\ntdbms\storeng\drs\include\record.inl>,
>> line=1447
>> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=>> MAXDATAROW'.
>> 2003-12-10 10:00:58.79 spid51 Error: 3624, Severity:
>> 20, State: 1.
>> 2003-12-10 10:00:58.79 spid51 Error: 3313, Severity:
>> 21, State: 2
>> 2003-12-10 10:00:58.79 spid51 Error while redoing
>> logged operation in database 'MYDATABASE'. Error at log
>> record ID (84983:2649:335)..
>> 2003-12-10 10:00:59.27 spid51 Error: 9004, Severity:
>> 23, State: 7
>> The client does not have a backup :o(. Does anyone
know
>> how I can recover from this situation? A repair option
>> would be much appreciated.
>> Thanks,
>> Bob
>
>.
>