Database Snapshot on SAN attached disks

Hi All

I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots

The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.

Is their some restriction/problem in using the database snapshot technology with SAN storage?

What edition of Sql server 2005 you have ? Database snapshot is possible only with Enterprise edition I believe
|||

SQL Server Enterprise 2005 SP2 is being used on all systems.

The snapshots are working against the local disks on the server but not against the SAN attached storage.

Regards

Nadreck

|||

Are the SAN attached drives formatted using the NTFS file system? Database Snapshots use NTFS Sparse Files for the underlying technology, and will not work under any other file system.

I have database snapshots on production clustered servers, using SAN storage, and haven't had any problems.

|||Your use of the term "SAN attached" has me wondering. There are Network Attached Storage (NAS) devices that often have their own proprietary file system and then there are Storage Area Networks (SAN) which are usually NTFS in the Windows world. Which type of storage are you referring to? Database snapshots require NTFS.
|||There is no difference between a "SAN" and a local drive to SQL, they both look like local drives. This must be a security issue on the directory.

If you are talking about a "NAS" drive, as in you access it using "\\servername\share", SQL does not support putting data/transaction files on share drives.

|||

The storage is on a true SAN with the disks attached by Fibre Channel and being presented as Local disk.

I have spoken with the SAN administrators and they have confirmed that the disks are formatted as NTFS

The error received when trying to create the sbnapshot was

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'filename'.

|||I think the login with which you have logged into Sql server(windows authentication) might not be having modify privilege to the folder where the Sparse file is being created ! also check if the sql service account has necessary privileges to that folder ! ! !

Thanxx
Deepak


|||

The logon accounts and service accounts both have local admin rights on the box so write permissions do not appear to be the issue. The mdf and ldf files for the database are both on SAN storage so SQL appears to have sufficient rights to write to these disks. Creating snapshots on local disk (with the same accounts) has no issue.

Obviously it may still be some permissions issue with the SAN but I cannot see any difference.

regards

Nadreck

Database Setting for text box and text area forms

I have a SQL Server database. The data from a table is populated in the table and can do a regular display query on a record without issue.

Problem is when I pull the data into a form the data doesn't show up in some form fields for editing.

I am building a backend for the manager to make updates and changes and this is vital. Does anyone know if it has something to do with a database setting or has had a similar issue in the past?

The reason I think its a database setting is becuase the same table converted into MS Access has no problem populating the text boxs and text areas.

Your help is much needed and appreciated.

Thanks.In case anyone is interested. You can solve this with a work around. Set a vaiable for the field item then use the response.write the variable to popluate the text box or text area.

Database Permissions

We have a system at work that copies (using DTS) over databases from
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?

Thanks,

Jeffjeffpuro@.yahoo.com (Jeff) wrote in message news:<7851a310.0401151322.4b8cf2e7@.posting.google.com>...
> We have a system at work that copies (using DTS) over databases from
> one SQL Server box to another every night. The copying process drops
> each target object and then recreates them. On the 2nd SQL Server box
> I have an account setup that is supposed to only have access to run
> queries on the databases that get copied over every night. However,
> because the DTS packages are dropping the objects first we are losing
> the table level permissions for this user, so this user can't access
> these databases the next day. Is there a way to automate resetting
> these permissions on each table in the databases? Perhaps I should be
> using replication as opposed to DTS packages for copying over entire
> databases? If I used replication, would this avoid losing the
> permissions that I need?
> Thanks,
> Jeff

You could script the table permissions, then execute that script after
copying the objects. Although it's not clear from your post why you
always drop and recreate the objects - DTS can copy only the data, or
you can put it in staging tables first, then insert into the final
production tables if you have some mapping logic. Replication would be
an option (perhaps snapshot replication), but it can be complex.

Simon

Database permission error

Andy,

I am new to SQL. It was hard just to figure out how to install it.

I know I am logged in as a Admin. in Windows.

I have the little server box with the round circle on it in the bottom right
corner(toolbar). The SQL Server Service Manager.

I tried to search for the sp_helpsrvrolemember command on my drive. It's
not on the server.

Any ideas?

Do anyone have any suggestions?

Thanks for your help,
T

"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:#3883ky6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> Are you connecting with a trusted connection or as sa. It sounds like you
> do not have the access required to create a database. Make sure you are
> connecting in as a member of the Administrators group on that machine so
you
> become part of the sysadmin (aka System Administrators) role. At a
minimum
> you need the dbcreator role level access. Running
> sp_helpsrvrolemember 'sysadmin'
> -- or --
> sp_helpsrvrolemember 'dbcreator'
> will tell you who belongs to these roles. Looking at the Query window
title
> bar will tell you who you are logged in as.
> <servername>.<database name>.<loginname> or <DOMAIN>\<username> (for Win
NT
> authentication)
>
> Reboot and sharing the folder will not help. You might want to check the
NT
> permissions though.
> --
> ************************************************** *****************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> ************************************************** *****************
> "Taishi" <taishi_bak@.hotmail.com> wrote in message
> news:%23ZocZIy6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> > Windows Pro
> > Receiving the following error:
> > CREATE DATABASE permission denied in database 'master'. Could not attach
> > database 'pubs' to file 'D:\Program Files\Microsoft SQL
> > Server\MSSQL$NETSDK\Data\pubs.mdf'.
> > Here are some things done to try to resolve the problem:
> > Reboot 4-5 times
> > Shared out the folder
> > Web shared the folder
> > Thanks for your help,
> > T.Taishi (taishi_bak@.hotmail.com) writes:
> I tried to search for the sp_helpsrvrolemember command on my drive. It's
> not on the server.

Searching the drive? There you will not find it. This is not a command
you run Explorer or the Command-Line Window.

This is a command that you run in Query Analyzer that comes with SQL Server.
From Query Analyzer you can pass any T-SQL command to SQL Server and
ser the result from it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am using WIN XP Pro and the SQL is the MSDE.

How can I check my access?

How can I resolve the following error?

CREATE DATABASE permission denied in database 'master'. Could not attach
database 'pubs' to file 'D:\Program Files\Microsoft SQL
Server\MSSQL$NETSDK\Data\pubs.mdf'.

Thanks,
T.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9486F27E141C6Yazorman@.127.0.0.1...
> Taishi (taishi_bak@.hotmail.com) writes:
> > I tried to search for the sp_helpsrvrolemember command on my drive.
It's
> > not on the server.
> Searching the drive? There you will not find it. This is not a command
> you run Explorer or the Command-Line Window.
> This is a command that you run in Query Analyzer that comes with SQL
Server.
> From Query Analyzer you can pass any T-SQL command to SQL Server and
> ser the result from it.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Taishi (taishi_bak@.hotmail.com) writes:
> I am using WIN XP Pro and the SQL is the MSDE.
> How can I check my access?
> How can I resolve the following error?
> CREATE DATABASE permission denied in database 'master'. Could not attach
> database 'pubs' to file 'D:\Program Files\Microsoft SQL
> Server\MSSQL$NETSDK\Data\pubs.mdf'.

Exactly what command did you run?

How did you log into SQL Server?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I haven't ran any commands. I just installed it and tried to access the
dbase from a web form. The error is in my first post. I installed the MSDE
2000 from the following web site:

ww.microsoft.com/downloads

Featured download
Microsoft SQL Server 2000
Desktop Engine(MSDE 2000) Release A

I don't have the SQL server disks.

Any ideas? What commands can I use to check access rights?

Thanks,
T.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948811073404Yazorman@.127.0.0.1...
> Taishi (taishi_bak@.hotmail.com) writes:
> > I am using WIN XP Pro and the SQL is the MSDE.
> > How can I check my access?
> > How can I resolve the following error?
> > CREATE DATABASE permission denied in database 'master'. Could not attach
> > database 'pubs' to file 'D:\Program Files\Microsoft SQL
> > Server\MSSQL$NETSDK\Data\pubs.mdf'.
> Exactly what command did you run?
> How did you log into SQL Server?
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Taishi (taishi_bak@.hotmail.com) writes:
> I haven't ran any commands. I just installed it and tried to access the
> dbase from a web form. The error is in my first post. I installed the
> MSDE 2000 from the following web site:
> ww.microsoft.com/downloads
> Featured download
> Microsoft SQL Server 2000
> Desktop Engine(MSDE 2000) Release A
> I don't have the SQL server disks.
> Any ideas? What commands can I use to check access rights?

Obviously then that web form tried run some commands. Exactly where
did this web form come from? Did you write it yourself, or was it included
in the download?

Is the web server on the same machine as MSDE? And what rights does the
account that web server is logged in have?

I would assume that the web form logs into MSDE with Windows authentication,
but it does not have sysadmin privileges. If you give the web-server account
administrator privileges in Windows, things might go better. You may have to
restart the web server for the change to have effect.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948A6458FF29BYazorman@.127.0.0.1...
> Taishi (taishi_bak@.hotmail.com) writes:
> > I haven't ran any commands. I just installed it and tried to access the
> > dbase from a web form. The error is in my first post. I installed the
> > MSDE 2000 from the following web site:
> > ww.microsoft.com/downloads
> > Featured download
> > Microsoft SQL Server 2000
> > Desktop Engine(MSDE 2000) Release A
> > I don't have the SQL server disks.
> > Any ideas? What commands can I use to check access rights?
> Obviously then that web form tried run some commands. Exactly where
> did this web form come from? Did you write it yourself, or was it included
> in the download?
> Is the web server on the same machine as MSDE? And what rights does the
> account that web server is logged in have?
> I would assume that the web form logs into MSDE with Windows
authentication,
> but it does not have sysadmin privileges. If you give the web-server
account
> administrator privileges in Windows, things might go better. You may have
to
> restart the web server for the change to have effect.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

I wrote the Web form myself. Everything is on the same machine.

I have to figure out your question about the Web Server account.

I am using IIS and ASP. (i feel dumb)

Is IIS my web server?

Thanks for help,
T.|||Taishi (taishi_bak@.hotmail.com) writes:
> I wrote the Web form myself. Everything is on the same machine.

Ah, but if you wrote the web form, you also included commands for
SQL Server one way or another.

> I have to figure out your question about the Web Server account.
> I am using IIS and ASP. (i feel dumb)
> Is IIS my web server?

I don't know much about IIS (or ASP), but I believe it is a web server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Do you know how to use Enterprise Manager?

I was able to find a trial version of the Enterprise Manager.

After reading my error today, I can see there is a problem with 2 dbases
'PUBS' and 'Master'

CREATE DATABASE permission denied in database 'master'. Could not attach
database 'pubs' to file 'D:\Program Files\Microsoft SQL
Server\MSSQL$NETSDK\Data\pubs.mdf'.

I can see the following in the MSDE manager:

sql server(my machine name\??SDK)
after I right click on 'PUBS' the following pops up.
--Users/Roles/Public
--public
--machine_name\aspnet
--guest

guest is the only one with 5 check marks going across from left to right for
the following:
Create Table, Create View, Create SP, Create Default, Create Rule

the following 3 are blank:
Create Function, Backup DB, and Backup Log

public has just 1 check mark on
Create SP

machine_name\aspnet is blank from left to right.

CREATE DBASE right is not displayed.

How can I solve my Permission denied error problem?

Thanks for the help,
Taishi

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AEF077388EYazorman@.127.0.0.1...
> Taishi (taishi_bak@.hotmail.com) writes:
> > I wrote the Web form myself. Everything is on the same machine.
> Ah, but if you wrote the web form, you also included commands for
> SQL Server one way or another.
> > I have to figure out your question about the Web Server account.
> > I am using IIS and ASP. (i feel dumb)
> > Is IIS my web server?
> I don't know much about IIS (or ASP), but I believe it is a web server.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Taishi (taishi_bak@.hotmail.com) writes:
> Do you know how to use Enterprise Manager?

Barely. :-) I prefer to carry most of my task by submitting T-SQL
commands from Enterprise Manager.

> I was able to find a trial version of the Enterprise Manager.
> After reading my error today, I can see there is a problem with 2 dbases
> 'PUBS' and 'Master'
> CREATE DATABASE permission denied in database 'master'. Could not attach
> database 'pubs' to file 'D:\Program Files\Microsoft SQL
> Server\MSSQL$NETSDK\Data\pubs.mdf'.

But you still have not told us what that web form is doing! Sorry, I
cannot give much precise help if you don't explain what you are trying
to do.

But for the record, master and pubs are both databases that comes with
SQL Server, but they are very different. pubs is a regular user database,
and is there for demonstration and training purposes. master on the
other hand, is, as the name, indicates the master database. In this
database, SQL Server stores all server-wide information, for instance
which databases there are.

So the permission error applies to the master database, not to pubs.
I can't tell why you get this error, because I don't know what you
are doing (and you refuse to tell me). But since pubs already exist,
you cannot attach it anyway.

Anyway, what I tried to tell you last time, is that the web server
needs to connect to SQL Server with sysadmin privileges to create
databases, and to do that it must either log in as "sa" using SQL
Server authentication, or log using Windows authentication with an
account that has Administrator rights in Windows, or which have been
added to the sysadmin role in SQL Server.

So the rights you should are those that the IIS account has.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

The web form is using a listbox to list items from SQL.

Here is my code:

Dim drStores As SqlClient.SqlDataReader

If Not IsPostBack Then

conPubs.Open()

drStores = cmdStores.ExecuteReader()

ddlStores.DataSource = drStores

ddlStores.DataTextField = "stor_name"

ddlStores.DataBind()

drStores.Close()

conPubs.Close()

End If

I didn't close my conPubs()-->MyConnection. That's one reason why I was
receiving the error before. But now my problem is that the ListBox is not
populating. The form just has a box that is empty. Seems simple for an
experienced programmer but I'm not at the level, yet.

In the Properties for MyConnection I have the following as my Connection
String:

workstation id=MyMachineName;packet size=4096;integrated security=SSPI;data
source="MyMachineName\vsdotnet";attachdbfilename="D:\Program Files\Microsoft
SQL Server\MSSQL$NETSDK\Data\pubs.mdf";persist security info=False;initial
catalog=pubs

In the Properties for MyCommand I have the following as my Connection
String:

workstation id=MyMachineName;packet size=4096;integrated security=SSPI;data
source="MyMachineName\vsdotnet";attachdbfilename="D:\Program Files\Microsoft
SQL Server\MSSQL$NETSDK\Data\pubs.mdf";persist security info=False;initial
catalog=pubs

I'm trying to grasp the concept but it's frustrating.

So, now you should know all about my web form.

Any ideas on how to fix my problem?

I will pay a million dollars for the answer to this problem. :-)

Thanks,
T.

End Sub

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948CEBA55DYazorman@.127.0.0.1...
> Taishi (taishi_bak@.hotmail.com) writes:
> > Do you know how to use Enterprise Manager?
> Barely. :-) I prefer to carry most of my task by submitting T-SQL
> commands from Enterprise Manager.
> > I was able to find a trial version of the Enterprise Manager.
> > After reading my error today, I can see there is a problem with 2 dbases
> > 'PUBS' and 'Master'
> > CREATE DATABASE permission denied in database 'master'. Could not attach
> > database 'pubs' to file 'D:\Program Files\Microsoft SQL
> > Server\MSSQL$NETSDK\Data\pubs.mdf'.
> But you still have not told us what that web form is doing! Sorry, I
> cannot give much precise help if you don't explain what you are trying
> to do.
> But for the record, master and pubs are both databases that comes with
> SQL Server, but they are very different. pubs is a regular user database,
> and is there for demonstration and training purposes. master on the
> other hand, is, as the name, indicates the master database. In this
> database, SQL Server stores all server-wide information, for instance
> which databases there are.
> So the permission error applies to the master database, not to pubs.
> I can't tell why you get this error, because I don't know what you
> are doing (and you refuse to tell me). But since pubs already exist,
> you cannot attach it anyway.
> Anyway, what I tried to tell you last time, is that the web server
> needs to connect to SQL Server with sysadmin privileges to create
> databases, and to do that it must either log in as "sa" using SQL
> Server authentication, or log using Windows authentication with an
> account that has Administrator rights in Windows, or which have been
> added to the sysadmin role in SQL Server.
> So the rights you should are those that the IIS account has.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Taishi (taishi_bak@.hotmail.com) writes:
> I didn't close my conPubs()-->MyConnection. That's one reason why I was
> receiving the error before. But now my problem is that the ListBox is not
> populating. The form just has a box that is empty. Seems simple for an
> experienced programmer but I'm not at the level, yet.
> In the Properties for MyConnection I have the following as my Connection
> String:
> workstation id=MyMachineName;packet size=4096;integrated
> security=SSPI;data
> source="MyMachineName\vsdotnet";attachdbfilename="D:\Program
> Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf";persist security
> info=False;initial catalog=pubs
> In the Properties for MyCommand I have the following as my Connection
> String:
> workstation id=MyMachineName;packet size=4096;integrated
> security=SSPI;data
> source="MyMachineName\vsdotnet";attachdbfilename="D:\Program
> Files\Microsoft SQL Server\MSSQL$NETSDK\Data\pubs.mdf";persist security
> info=False;initial catalog=pubs

Ah, we're on to something here. Take out that "attachdbfilename" from
your Connection string. I see in MSDN Library, that this property is
actually supported for SqlClient, although I cannot understand the point
with it. Anyway, since pubs is already attached, you have no reason
to meddle with it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp