SQL SQLExpress: February 2012

Saturday, February 25, 2012

.NET 1.xxx and .NET 2.0 beta for SQLRS

I am looking to put the SQL2005 Xpress on my dev machine, which already had
2K and RS + .NET 1.xxx.
I understand I need to remove .NET 1.xxx to install 2.0. How will this
affect SQLRS?
I have RSSP2 installed.
Thanks,
TonyRS requires 1.1. I don't think you have to remove it but you will definitely
have some mucking around to do. You will have to have asp.net working both
with 1.1 and 2.0 framework (which you can do) and you will have to have the
RS sites in asp.net running with 1.1 framework.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:6B8081C2-4F42-45FA-9E73-0C12F51AA3D1@.microsoft.com...
>I am looking to put the SQL2005 Xpress on my dev machine, which already had
> 2K and RS + .NET 1.xxx.
> I understand I need to remove .NET 1.xxx to install 2.0. How will this
> affect SQLRS?
> I have RSSP2 installed.
> Thanks,
> Tony|||Bruce,
Thanks for the reply. The reason for my question was because SQL2005
requires .NET 2.0 and .NET 2.0 has refused to install as it detected 1.xxx is
present. How can I 'fool' .NET 2.0 it into thinking 1.xxx is not there.
Does this also mean I cannot use SQL2005 with SQLRS server?
"Bruce L-C [MVP]" wrote:
> RS requires 1.1. I don't think you have to remove it but you will definitely
> have some mucking around to do. You will have to have asp.net working both
> with 1.1 and 2.0 framework (which you can do) and you will have to have the
> RS sites in asp.net running with 1.1 framework.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
> news:6B8081C2-4F42-45FA-9E73-0C12F51AA3D1@.microsoft.com...
> >I am looking to put the SQL2005 Xpress on my dev machine, which already had
> > 2K and RS + .NET 1.xxx.
> > I understand I need to remove .NET 1.xxx to install 2.0. How will this
> > affect SQLRS?
> > I have RSSP2 installed.
> >
> > Thanks,
> >
> > Tony
>
>|||I doubt that RS will install with SQL 2005. It checks versions of SQL Server
and I would think it wouldn't install with it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
news:85080A64-B930-4767-B8CE-AC0DF3BEF00D@.microsoft.com...
> Bruce,
> Thanks for the reply. The reason for my question was because SQL2005
> requires .NET 2.0 and .NET 2.0 has refused to install as it detected 1.xxx
> is
> present. How can I 'fool' .NET 2.0 it into thinking 1.xxx is not there.
> Does this also mean I cannot use SQL2005 with SQLRS server?
> "Bruce L-C [MVP]" wrote:
>> RS requires 1.1. I don't think you have to remove it but you will
>> definitely
>> have some mucking around to do. You will have to have asp.net working
>> both
>> with 1.1 and 2.0 framework (which you can do) and you will have to have
>> the
>> RS sites in asp.net running with 1.1 framework.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
>> news:6B8081C2-4F42-45FA-9E73-0C12F51AA3D1@.microsoft.com...
>> >I am looking to put the SQL2005 Xpress on my dev machine, which already
>> >had
>> > 2K and RS + .NET 1.xxx.
>> > I understand I need to remove .NET 1.xxx to install 2.0. How will this
>> > affect SQLRS?
>> > I have RSSP2 installed.
>> >
>> > Thanks,
>> >
>> > Tony
>>|||I think there was a problem installing the .NET 2.0 with 1.x in the past. I
think you just don´t have the actual version of v2.0 which can be installed
side-by-side with v1.x. Try downloading a new one and see if that helps.
HTH, Jens SUessmeyer.
--
http://www.sqlserver2005.de
--
"Logicalman" <tony9scott45us@.com7cast.net34> schrieb im Newsbeitrag
news:85080A64-B930-4767-B8CE-AC0DF3BEF00D@.microsoft.com...
> Bruce,
> Thanks for the reply. The reason for my question was because SQL2005
> requires .NET 2.0 and .NET 2.0 has refused to install as it detected 1.xxx
> is
> present. How can I 'fool' .NET 2.0 it into thinking 1.xxx is not there.
> Does this also mean I cannot use SQL2005 with SQLRS server?
> "Bruce L-C [MVP]" wrote:
>> RS requires 1.1. I don't think you have to remove it but you will
>> definitely
>> have some mucking around to do. You will have to have asp.net working
>> both
>> with 1.1 and 2.0 framework (which you can do) and you will have to have
>> the
>> RS sites in asp.net running with 1.1 framework.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
>> news:6B8081C2-4F42-45FA-9E73-0C12F51AA3D1@.microsoft.com...
>> >I am looking to put the SQL2005 Xpress on my dev machine, which already
>> >had
>> > 2K and RS + .NET 1.xxx.
>> > I understand I need to remove .NET 1.xxx to install 2.0. How will this
>> > affect SQLRS?
>> > I have RSSP2 installed.
>> >
>> > Thanks,
>> >
>> > Tony
>>|||What operating system are you using? SxS will only work on Windows 2003.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uCvLQ0kTFHA.228@.TK2MSFTNGP12.phx.gbl...
>I think there was a problem installing the .NET 2.0 with 1.x in the past. I
>think you just don´t have the actual version of v2.0 which can be installed
>side-by-side with v1.x. Try downloading a new one and see if that helps.
> HTH, Jens SUessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Logicalman" <tony9scott45us@.com7cast.net34> schrieb im Newsbeitrag
> news:85080A64-B930-4767-B8CE-AC0DF3BEF00D@.microsoft.com...
>> Bruce,
>> Thanks for the reply. The reason for my question was because SQL2005
>> requires .NET 2.0 and .NET 2.0 has refused to install as it detected
>> 1.xxx is
>> present. How can I 'fool' .NET 2.0 it into thinking 1.xxx is not there.
>> Does this also mean I cannot use SQL2005 with SQLRS server?
>> "Bruce L-C [MVP]" wrote:
>> RS requires 1.1. I don't think you have to remove it but you will
>> definitely
>> have some mucking around to do. You will have to have asp.net working
>> both
>> with 1.1 and 2.0 framework (which you can do) and you will have to have
>> the
>> RS sites in asp.net running with 1.1 framework.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Logicalman" <tony9scott45us@.com7cast.net34> wrote in message
>> news:6B8081C2-4F42-45FA-9E73-0C12F51AA3D1@.microsoft.com...
>> >I am looking to put the SQL2005 Xpress on my dev machine, which already
>> >had
>> > 2K and RS + .NET 1.xxx.
>> > I understand I need to remove .NET 1.xxx to install 2.0. How will this
>> > affect SQLRS?
>> > I have RSSP2 installed.
>> >
>> > Thanks,
>> >
>> > Tony
>>
>

.net 1.1 SqlDbType accepts varchar(MAX)

My app uses .net 1.1 and MS SQL 2005 as backend. How to add a SqlParamter that is varchar(MAX)?

SqlParameter myParameter = new SqlParameter("@.Description",SqlDbType.VarChar,whatToPutHere?,ParameterDirection.Input,
true,0,0,"Whatever",DataRowVersion.Current,"Whatever");

That's the size position, right? Put the max value you expect to have. Max allowable is 8000

|||

Try:

SqlParameter myParameter = new SqlParameter("@.Description",SqlDbType.VarChar);

|||

Motley:
SqlDbType.VarChar

I'm not positive about this, but make sure that if you leave the VarChar declaration w/o a length parm that it won't default to VarChar(1) (ie, max 1 char). I think it may do this but I could be wrong. I am pretty sure that if you declare a variable as

declare @.v varchar

that it sets the max size to 1, for example (but I'mnot anywhere where I can double check that statment, though<g>)

|||

Motley:
SqlDbType.VarChar

declare @.v varchar

that it sets the max size to 1, for example (but I'mnot anywhere where I can double check that statment, though<g>)

.NET 1.1 running on SQL Server 2005

Hi, I am migrating my client's site from SQL 2k to 2005. The site was built on .NET 1.1

In order to run SQL 2005, do we need to upgrade to .NET 2.0 in order to run the site?

If we do need to upgrade to .net 2.0, is there any way to get around the problem and run it on .NET 1.1. This is because we have a lot of 3rd party componenet which only support .NET 1.1

Thanks in advance.

Paul

No.|||You would need the 2.0 framework for sure. I think the SQL 2005 installation wizard would detect and install the framework for you.You dont need to upgrade your application to VS 2005, however.

.net 1.1 and SQL Server 2005

Hi,

I run .net 1.1, VS2003 with SQL Server Enterprise Manager v 8.0 on my home XP Pro machine.

I have recently purchased some hosting with SQL Server 2005 capabilities. I was in a real rush but I should have checked I suppose.

I would love to be able to use my Enterprise Manager but obviously that wont work as it isnt compatible with 2005. It would be preferable not to have to use the web based admin and I have been told I have to install Microsoft SQL Server Management Studio Express, which requires .net 2.0 to be installed. It seems a bit of a mess really.

Can anyone enlighten me as to what options I have?

Thanks,

Pete

You're already looking at the best option, but incorrectly assuming it's a mess. It isn't. The two framework versions are independent and play fine together, and just doing the default installs should give you wahat you need in not much more time than it takes to download the files.

Jeff

|||

ah OK Jeff thanks,

I shall give it a go!

Pete

|||

Can anyone tell me why 2003 isn't compatible with 2005?

Not a gripe, just curious...

|||

2003 what and 2005 what?

Jeff

|||

Oops,

Well, the whole .net 2 / SQL Server 2005 thing.

Why cant I log in to 2005 server using my Enterprise manager 8.0 ... :)

.NET 1.1 and .NET 2 Perceives DB data differently

I'm hoping someone can help me out - at least by pointing me to who I can ask, if not answering the question directly.

I have some encrypted values in a SQL Server 2000 database that I unencrypt and use in a website that I just converted from .NET 1.1 to .NET 2.

The data is pulled from the database using standard ADO with no changes between the .NET 1.1 version and the .NET 2 version - yet for some data entries, when the identical value is pulled by the .NET 2 code it is changed or shorted.

For example:

1.1 code traces out a value pulled from the db as:

??淨琳d搈把???媑????????

The same value pulled from the database by .NET 2 looks like this:

??淨琳d搈把媑?????

Do you know why the database value would be interpreted diferently by .NET 2 than by .NET 1.1? How can I bring this in sync so that both 1.1 sites and 2 sites can use the same data?

Now that's a thought. It would explain a shortening.

The column both are pulling from is an nvarchar... and the nvarchar/varchar distinction is not, as far as I know, found in C# strings. So would ADO.NET 2 convert an nvarchar to a varchar while the exact same code in ADO.NET 1.1 does not?

How would I test to see if this is happening?

I think though as a guess that both items are returning nvarchar characters.. while its not visible in what I cut and pasted into this site, there were chinese- and greek-looking characters evident in both versions of the encrypted data which would seem to me like they were both coming out of the database as nvarchar.

|||

Now that's a thought. It would explain a shortening.

How would I test to see if this is happening?

|||oops... I think something just stuttered. :-)|||

I found out the answer... and it is NOT related to ADO.

Why I don't know why the encrypted data appeard differently in .NET 2 when debugged or traced out from the database, I did discover that the .NET 2 encryption has tightened what it will tolerate as acceptable unicode. .NET 1.1 would allow UTF8 encoding... and if it didn't exactly match, it would allow a fudge estimate. .NET 2 does not allow that any more. To get around this, rather than using typical encoding, one needs to make sure that 64 base encoding is used when creating string data from encrypted values.

Information on this can be found here:

http://blogs.msdn.com/shawnfa/archive/2005/11/10/491431.aspx

and here:

http://blogs.msdn.com/shawnste/archive/2005/03/24/401871.aspx

I needed to change my encryption code to use Convert.ToBase64String and Convert.FromBase64String rather than relying on the standard encoding classes. Once I did that, the .NET 2 code could encrypt and decrypt with consistancy with itself, but I could not maintain backwards compatability with the encrypted data already in the database - when it included the .NET 1.1 fudging.

|||

I do not know exactly but it is possible that if encrypt/decrypt on web ASP code it is possible that both version use different key to encrypt/decrypt data, You probably have to make your own key and do encryption/decryption on SQL server side which will always work the same way.

Thanks

|||

Thanks for answering!

Both use the same key to decrypt. But what's interesting here is that the difference is evident before the decryption is run. The raw data from the database looks different (and is shorter) when pulled by ADO .NET 2 from when its pulled by ADO .NET 1.1.

|||

Is it possible that one is returned as varchar and second nvarchar?

Thanks

|||

Now that's a thought. It would explain a shortening.

How would I test to see if this is happening?

I think though that both items are returning nvarchar characters.. while its not visible in what I cut and pasted into this site, there were chinese- and greek-looking characters evident in both versions of the encrypted data which would seem to me like they were both coming out of the database as nvarchar.

.NET /script/T-SQL Code to deliberately cause Cluster Failover

I'm looking for help in locating a script that would allow deliberately
causing a cluster to failover. (For demonstration and testing purposes.)
And ideas or suggestions would be appreciated.
Thanks,
Arnie Rowland
You can use the CLUSTER.EXE command from the command line prompt to force a
failure. Look up Cluster.exe in the on-line help files for Windows Server.
Everything you can do from the GUI, you can do from the command-line.
Example:
Cluster MyClusterName resource MYSQLCluster Resource /fail
Will cause the specific resource to fail. You can then watch how the
cluster reacts.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uyoFczoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I'm looking for help in locating a script that would allow deliberately
> causing a cluster to failover. (For demonstration and testing purposes.)
> And ideas or suggestions would be appreciated.
> Thanks,
> Arnie Rowland
>
>
|||Thanks Geoff,
I forget that it was also command line! Du-oh...
-Arnie
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:#7m#$FpHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> You can use the CLUSTER.EXE command from the command line prompt to force
a
> failure. Look up Cluster.exe in the on-line help files for Windows
Server.
> Everything you can do from the GUI, you can do from the command-line.
> Example:
> Cluster MyClusterName resource MYSQLCluster Resource /fail
> Will cause the specific resource to fail. You can then watch how the
> cluster reacts.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:uyoFczoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
>
|||That's OK. I goofed up the example and put RESOURCE in there twice. I
should know better than to answer posts right after lunch.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OBwY0HpHFHA.3612@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Geoff,
> I forget that it was also command line! Du-oh...
> -Arnie
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:#7m#$FpHFHA.1172@.TK2MSFTNGP12.phx.gbl...
force[vbcol=seagreen]
> a
> Server.
deliberately[vbcol=seagreen]
purposes.)
>
|||Yeah, I caught that part. ;-)
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ePtnyJpHFHA.2740@.TK2MSFTNGP12.phx.gbl...
> That's OK. I goofed up the example and put RESOURCE in there twice. I
> should know better than to answer posts right after lunch.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OBwY0HpHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> force
> deliberately
> purposes.)
>

.Net <-> SqlServer admin actions

Hi,
Is it possible in a programmatic way (.Net) to tell the SqlServer 2000 to do
the following things?
1.. Backup the transaction protocol
2.. Restore a database backup
3.. Run the actions from the transaction protocol
This scenario is useful for me to get the database state in case of an error
(application error and not SQL or hardware error) so that I can reproduce
and correct the error.
Any answer could help me a lot.
Sincerely,
George.Hi
Yes, there are T-SQL commands as BACKUP LOG ,RESTORE DATABASE ,BACKUP
DATABASE for more details please refer to the BOL. Personally , I prefer
calling a stored procedure from .NET for instance that does such jobs .

> 3.. Run the actions from the transaction protocol
What do you mean?
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:OpyWjCtOGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is it possible in a programmatic way (.Net) to tell the SqlServer 2000 to
> do the following things?
>
> 1.. Backup the transaction protocol
> 2.. Restore a database backup
> 3.. Run the actions from the transaction protocol
>
> This scenario is useful for me to get the database state in case of an
> error (application error and not SQL or hardware error) so that I can
> reproduce and correct the error.
>
> Any answer could help me a lot.
>
> Sincerely,
> George.
>|||Hi,
With point 3 I wanted to say that after restoring the DB I have to restore
the transaction protocol in order to get the db state for which the error
occurred.
George.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMGDSFtOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi
> Yes, there are T-SQL commands as BACKUP LOG ,RESTORE DATABASE ,BACKUP
> DATABASE for more details please refer to the BOL. Personally , I prefer
> calling a stored procedure from .NET for instance that does such jobs .
>
> What do you mean?
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:OpyWjCtOGHA.312@.TK2MSFTNGP12.phx.gbl...
>|||Hi
Ok, yes you can do that as well. There is an option of RESTORE LOG
DataBaseName FROM Disk
WITH RECOVERY, STOPAT = 'Jul 1, 2005 10:00 AM'
Agaim , much more details you can find in the BOL
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:O2u3xatOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> With point 3 I wanted to say that after restoring the DB I have to restore
> the transaction protocol in order to get the db state for which the error
> occurred.
>
> George.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMGDSFtOGHA.4052@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Uri,
Your answer helps me a lot. I will try to implement these things.
George.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ua9GzgtOGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi
> Ok, yes you can do that as well. There is an option of RESTORE LOG
> DataBaseName FROM Disk
> WITH RECOVERY, STOPAT = 'Jul 1, 2005 10:00 AM'
> Agaim , much more details you can find in the BOL
>
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:O2u3xatOGHA.1088@.tk2msftngp13.phx.gbl...
>

.neRe: Store procedures & Functions

hi,

what is the different between store procedure and function?

when it is right to use sp and when functions?

thanks in advanced

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233961&SiteID=1

This is link for post about difference between UDF and SP. You can use scalar-valued function anywhere when T-SQL command is expecting a value.

Thanks.

|||ok thanks for the help :)

.ndf

Is there a way to attach an .ndf file from database A to database B and move whatever tables and indexes in the data file to database B?You can do it using SP_ATTACH_DB, for instance :
[/B]
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'F:\mssql7\data\My_Data.mdf',
@.filename2 = N'F:\mssql7\data\My_Data.Ndf',
@.filename3 = N'F:\mssql7\data\My_log.ldf'
[/B]
Refer to books online for more information on the SP.

.mdf/.ldf on a NAS

I'm wondering if it's a good idea to put datafiles on a NAS and to run the
DB Servers without local disks.
Seems risky to me, but I know the topic has come up and (I hope) Microsoft
has an article on the subject.
Thanks,
JayJay,
http://support.microsoft.com/kb/304261
RLF
"Jay" <nospan@.nospam.org> wrote in message
news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
> I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> DB Servers without local disks.
> Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> has an article on the subject.
> Thanks,
> Jay
>|||I'd recommend against placing the database files on NAS.
NAS is based on CIFS and CIFS is not a good protocol for block I/Os. In
addition, a host with a mapped network drive is a CIFS client, and if you
read the CIFS specs carefully, data can be cached on the CIFS client side
(i.e. SQL Server host if you map a network drive there). I'm not comfortable
with what that CIFS caching may do to your SQL database integrity.
Linchi
"Jay" wrote:
> I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> DB Servers without local disks.
> Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> has an article on the subject.
> Thanks,
> Jay
>
>|||I misspoke, not a NAS, a SAN (if it makes a difference)
"Jay" <nospan@.nospam.org> wrote in message
news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
> I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> DB Servers without local disks.
> Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> has an article on the subject.
> Thanks,
> Jay
>|||NAS = Network Attached Storage
SAN = Storage Area Network
They are different stuff.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:eZduXt$GIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> I'm wondering if it's a good idea to put datafiles on a NAS and to run
>> the DB Servers without local disks.
>> Seems risky to me, but I know the topic has come up and (I hope)
>> Microsoft has an article on the subject.
>> Thanks,
>> Jay
>|||Yes, SAN is OK, since it has dedicated HW and network with protocols designed for this. Consider
what you have at the other end, though, and how the SAN is setup. It isn't uncommon to end up with
your database files "in the pool" with no control for you regarding RAID levels and whatever else
get shared with your data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:eZduXt$GIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> I'm wondering if it's a good idea to put datafiles on a NAS and to run the DB Servers without
>> local disks.
>> Seems risky to me, but I know the topic has come up and (I hope) Microsoft has an article on the
>> subject.
>> Thanks,
>> Jay
>|||I'd venture to say that today most databases in large enterprises run on some
kind of SANs, mostly fibre-channel SANs.
Linchi
"Jay" wrote:
> I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
> > I'm wondering if it's a good idea to put datafiles on a NAS and to run the
> > DB Servers without local disks.
> >
> > Seems risky to me, but I know the topic has come up and (I hope) Microsoft
> > has an article on the subject.
> >
> > Thanks,
> > Jay
> >
>
>|||SAN will be fine, it's what we do.
SAN and NAS have almost nothing in common, unless you're dyslexic :-)
"Jay" <nospan@.nospam.org> wrote in message
news:eZduXt$GIHA.1212@.TK2MSFTNGP05.phx.gbl...
>I misspoke, not a NAS, a SAN (if it makes a difference)
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23aLZrJ$GIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> I'm wondering if it's a good idea to put datafiles on a NAS and to run
>> the DB Servers without local disks.
>> Seems risky to me, but I know the topic has come up and (I hope)
>> Microsoft has an article on the subject.
>> Thanks,
>> Jay
>

.MDF with MIcrosoft Excel.........

Is there a way to get external data in excel from a backup of a mdf off of the SQL Server??? :confused:Sure. Restore the backup to an instance of SQL Server, then get the data from that instance.|||I have to load it into a microsoft server, meaning I can't take it off a backup (not needing the server at all).|||That's right. But do not dispair if you do not have sql server there are a couple free or cheap options.

.mdf to .mdb

My web host only supports .mdb database files, so is there a way to convert .mdf to .mdb with a program? Or would I have to do it by hand?

Thank you.

I am not aware that such a program exists.

Maybe you can use SQL Server DTS or SQL Server Transformation Services to download your database into a .mdb format.

|||Try to use this.
http://www.data-conversions.net/products.php?prod_num=1&&dest=MENU&&ID=100|||

I was having the same issue and followed these steps...

1. In your ODBC connections add a SQL server connection to your current SQL server and database.

2. Create a new Access Database

3. Import from ODBC source and select all tables.

The tables will import with a dbo_ prefix, but all of the structure and information will be there. It does not carry over the table relationships either.

Nick

.mdf to .mdb

My web host only supports .mdb database files, so is there a way to convert .mdf to .mdb with a program? Or would I have to do it by hand?

Thank you.

I am not aware that such a program exists.

Maybe you can use SQL Server DTS or SQL Server Transformation Services to download your database into a .mdb format.

|||Try to use this.
http://www.data-conversions.net/products.php?prod_num=1&&dest=MENU&&ID=100|||

I was having the same issue and followed these steps...

1. In your ODBC connections add a SQL server connection to your current SQL server and database.

2. Create a new Access Database

3. Import from ODBC source and select all tables.

The tables will import with a dbo_ prefix, but all of the structure and information will be there. It does not carry over the table relationships either.

Nick

.MDF recovery after data failure

Hi,
I recently had a problem with a database which resulted in restoring the
database from backup. The .mdf and .ldf files were restored, however I could
not reattach the database.
I attempted to create a new database, stop SQL Server, and copy over the
.mdf and .ldf files which put the database into Suspect/Online. I was not
able to bring the database back online.
Further, after changing the status in to Emergency Mode and attempting a
DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
error with some extra data.
I also tried a third party .MDF data recovery software which yielded 100KB
worth of one table instead for a 100MB database. Any advice?Hi
I am not sure where the problem is if you restored the database from backup!
I can only assume there was data loss because either didn't have full
recovery mode and/or log backups available to do point in time recovery?
If your database is suspect you could try, but make sure that you copy the
files first
http://tinyurl.com/jhv9k
John
"t-o-m-h-VA" wrote:
> Hi,
> I recently had a problem with a database which resulted in restoring the
> database from backup. The .mdf and .ldf files were restored, however I could
> not reattach the database.
> I attempted to create a new database, stop SQL Server, and copy over the
> .mdf and .ldf files which put the database into Suspect/Online. I was not
> able to bring the database back online.
> Further, after changing the status in to Emergency Mode and attempting a
> DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> error with some extra data.
> I also tried a third party .MDF data recovery software which yielded 100KB
> worth of one table instead for a 100MB database. Any advice?|||The recovered MDF file was pulled from disk backup and did not have full
recovery, it was the exact file from the server. The log file is also there
but i tried the steps in the URL,
1.) Reset the status from "Suspect':
EXEC sp_resetstatus 'myDatabaseName'
2.) Set the database property "allow updates':
exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
3.) Set the database to "Emergency Mode":
UPDATE master..sysdatabases SET status=-32768 WHERE
name='myDatabseName'
4.) Rebuild the database log file:
dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ myDatabaseName2_log.LDF')
However, when I try to do a DTS Import it will not authenicate the SYSTEM
user saying initalization failed. I suspect the .mdf file is corrupt
somehow, is there any recovery tools to view the MDF file?
"John Bell" wrote:
> Hi
> I am not sure where the problem is if you restored the database from backup!
> I can only assume there was data loss because either didn't have full
> recovery mode and/or log backups available to do point in time recovery?
> If your database is suspect you could try, but make sure that you copy the
> files first
> http://tinyurl.com/jhv9k
> John
>
> "t-o-m-h-VA" wrote:
> > Hi,
> >
> > I recently had a problem with a database which resulted in restoring the
> > database from backup. The .mdf and .ldf files were restored, however I could
> > not reattach the database.
> >
> > I attempted to create a new database, stop SQL Server, and copy over the
> > .mdf and .ldf files which put the database into Suspect/Online. I was not
> > able to bring the database back online.
> >
> > Further, after changing the status in to Emergency Mode and attempting a
> > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> > error with some extra data.
> >
> > I also tried a third party .MDF data recovery software which yielded 100KB
> > worth of one table instead for a 100MB database. Any advice?|||Hi
Check to see if you have orphaned users see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#XSLTH3182121121120121120120 also there is link regarding resolving permissions issues.
John
"t-o-m-h-VA" wrote:
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
> > Hi
> >
> > I am not sure where the problem is if you restored the database from backup!
> > I can only assume there was data loss because either didn't have full
> > recovery mode and/or log backups available to do point in time recovery?
> >
> > If your database is suspect you could try, but make sure that you copy the
> > files first
> > http://tinyurl.com/jhv9k
> >
> > John
> >
> >
> >
> > "t-o-m-h-VA" wrote:
> >
> > > Hi,
> > >
> > > I recently had a problem with a database which resulted in restoring the
> > > database from backup. The .mdf and .ldf files were restored, however I could
> > > not reattach the database.
> > >
> > > I attempted to create a new database, stop SQL Server, and copy over the
> > > .mdf and .ldf files which put the database into Suspect/Online. I was not
> > > able to bring the database back online.
> > >
> > > Further, after changing the status in to Emergency Mode and attempting a
> > > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
> > > error with some extra data.
> > >
> > > I also tried a third party .MDF data recovery software which yielded 100KB
> > > worth of one table instead for a 100MB database. Any advice?|||>is there any recovery tools to view the MDF file?
http://www.officerecovery.com/mssql/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"t-o-m-h-VA" <tomhVA@.discussions.microsoft.com> wrote in message
news:31D481E6-B762-4090-8A79-E469E3CD661D@.microsoft.com...
> The recovered MDF file was pulled from disk backup and did not have full
> recovery, it was the exact file from the server. The log file is also there
> but i tried the steps in the URL,
> 1.) Reset the status from "Suspect':
> EXEC sp_resetstatus 'myDatabaseName'
> 2.) Set the database property "allow updates':
> exec sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE
> 3.) Set the database to "Emergency Mode":
> UPDATE master..sysdatabases SET status=-32768 WHERE
> name='myDatabseName'
> 4.) Rebuild the database log file:
> dbcc rebuild_log ('myDatabaseName','C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ myDatabaseName2_log.LDF')
> However, when I try to do a DTS Import it will not authenicate the SYSTEM
> user saying initalization failed. I suspect the .mdf file is corrupt
> somehow, is there any recovery tools to view the MDF file?
>
> "John Bell" wrote:
>> Hi
>> I am not sure where the problem is if you restored the database from backup!
>> I can only assume there was data loss because either didn't have full
>> recovery mode and/or log backups available to do point in time recovery?
>> If your database is suspect you could try, but make sure that you copy the
>> files first
>> http://tinyurl.com/jhv9k
>> John
>>
>> "t-o-m-h-VA" wrote:
>> > Hi,
>> >
>> > I recently had a problem with a database which resulted in restoring the
>> > database from backup. The .mdf and .ldf files were restored, however I could
>> > not reattach the database.
>> >
>> > I attempted to create a new database, stop SQL Server, and copy over the
>> > .mdf and .ldf files which put the database into Suspect/Online. I was not
>> > able to bring the database back online.
>> >
>> > Further, after changing the status in to Emergency Mode and attempting a
>> > DBCC CHECKDB, it still resulted in failures. The error code was 823 I/O
>> > error with some extra data.
>> >
>> > I also tried a third party .MDF data recovery software which yielded 100KB
>> > worth of one table instead for a 100MB database. Any advice?

.MDF recovery after data failure

.mdf not properly shut down.

Each night our SQL server is stopped and .mdf files are copied to a backup
server. When I try to reattach the files it claims that they were not
properly shut down. If I add the transaction file all is well. I was informed
by an expert at Microsoft that this should not be happening. Any ideas why I
am not getting a clean shutdown. The SQL server is stopped at night when
there is not activity. TIA> I was informed
> by an expert at Microsoft that this should not be happening.
What somebody say is irrelevant. What matters is what the product documentation say. If you can find
a statement in the product documentation that this is supposed to work, you can file a bug report
with MS (and not having to pay for it, since it would be a bug). However, I don't think the
documentation states anywhere that this is supported. For instance, the documentation say for
sp_attach_db that the database need to be detached first.
Why not just do a backup? The BACKUP and RESTORE commands are fully supported and BACKUP is all
on-line.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
> Each night our SQL server is stopped and .mdf files are copied to a backup
> server. When I try to reattach the files it claims that they were not
> properly shut down. If I add the transaction file all is well. I was informed
> by an expert at Microsoft that this should not be happening. Any ideas why I
> am not getting a clean shutdown. The SQL server is stopped at night when
> there is not activity. TIA|||Greg wrote:
> Each night our SQL server is stopped and .mdf files are copied to a backup
> server. When I try to reattach the files it claims that they were not
> properly shut down. If I add the transaction file all is well. I was informed
> by an expert at Microsoft that this should not be happening. Any ideas why I
> am not getting a clean shutdown. The SQL server is stopped at night when
> there is not activity. TIA
A SQL Server database consists of, at minimum, two files - a data file
(MDF) and a transaction log file (LDF). You don't have a complete
database without all of its files.
Why are you shutting down SQL Server to do backups? The accepted method
for doing backups is to use the BACKUP DATABASE command to do "hot"
backups of the database to a disk file, then backup that disk file to
tape (or other media).
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your response. I know I have that option. Should I be concerned
about what is happening. I am wondering if I have corruption in my database
or log file. I have run dbcc checkdb and it reports all is well. It just
concerns me that something is happening that an expert says should not be
happening. I will do more research.
"Tibor Karaszi" wrote:
> > I was informed
> > by an expert at Microsoft that this should not be happening.
> What somebody say is irrelevant. What matters is what the product documentation say. If you can find
> a statement in the product documentation that this is supposed to work, you can file a bug report
> with MS (and not having to pay for it, since it would be a bug). However, I don't think the
> documentation states anywhere that this is supported. For instance, the documentation say for
> sp_attach_db that the database need to be detached first.
> Why not just do a backup? The BACKUP and RESTORE commands are fully supported and BACKUP is all
> on-line.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
> > Each night our SQL server is stopped and .mdf files are copied to a backup
> > server. When I try to reattach the files it claims that they were not
> > properly shut down. If I add the transaction file all is well. I was informed
> > by an expert at Microsoft that this should not be happening. Any ideas why I
> > am not getting a clean shutdown. The SQL server is stopped at night when
> > there is not activity. TIA
>|||Are you doing an attach single file? This should work.Try using
sp_attach_single_file_db.
If you still get this error I would question how you are shutting down the
SQL Server? Perhaps you are using the kill command from a command prompt or
task manager.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
> Each night our SQL server is stopped and .mdf files are copied to a backup
> server. When I try to reattach the files it claims that they were not
> properly shut down. If I add the transaction file all is well. I was
> informed
> by an expert at Microsoft that this should not be happening. Any ideas why
> I
> am not getting a clean shutdown. The SQL server is stopped at night when
> there is not activity. TIA|||> Should I be concerned
> about what is happening. I am wondering if I have corruption in my database
> or log file. I have run dbcc checkdb and it reports all is well.
I wouldn't be concerned. Especially since CHECKDB comes out clean.
> It just
> concerns me that something is happening that an expert says should not be
> happening.
I don't know how this expert is, but if that person claims what you are doing is OK, then perhaps
he/she can comment on what is happening. To me, what you see seems normal. SQL Server has recovery
work to do when it starts up, and without a log file, well... It has been this way since version
1.0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:81BAA9CC-ECC6-4C42-B8BF-2EBC1F2757DF@.microsoft.com...
> Thanks for your response. I know I have that option. Should I be concerned
> about what is happening. I am wondering if I have corruption in my database
> or log file. I have run dbcc checkdb and it reports all is well. It just
> concerns me that something is happening that an expert says should not be
> happening. I will do more research.
> "Tibor Karaszi" wrote:
>> > I was informed
>> > by an expert at Microsoft that this should not be happening.
>> What somebody say is irrelevant. What matters is what the product documentation say. If you can
>> find
>> a statement in the product documentation that this is supposed to work, you can file a bug report
>> with MS (and not having to pay for it, since it would be a bug). However, I don't think the
>> documentation states anywhere that this is supported. For instance, the documentation say for
>> sp_attach_db that the database need to be detached first.
>> Why not just do a backup? The BACKUP and RESTORE commands are fully supported and BACKUP is all
>> on-line.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
>> > Each night our SQL server is stopped and .mdf files are copied to a backup
>> > server. When I try to reattach the files it claims that they were not
>> > properly shut down. If I add the transaction file all is well. I was informed
>> > by an expert at Microsoft that this should not be happening. Any ideas why I
>> > am not getting a clean shutdown. The SQL server is stopped at night when
>> > there is not activity. TIA
>>|||I have a batch process that stops the services. I tried the
sp_attach_single_file_db and does not work. It seems that I must have the
transaction file.
"Hilary Cotter" wrote:
> Are you doing an attach single file? This should work.Try using
> sp_attach_single_file_db.
> If you still get this error I would question how you are shutting down the
> SQL Server? Perhaps you are using the kill command from a command prompt or
> task manager.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
> > Each night our SQL server is stopped and .mdf files are copied to a backup
> > server. When I try to reattach the files it claims that they were not
> > properly shut down. If I add the transaction file all is well. I was
> > informed
> > by an expert at Microsoft that this should not be happening. Any ideas why
> > I
> > am not getting a clean shutdown. The SQL server is stopped at night when
> > there is not activity. TIA
>
>|||I just have to ask: Why are you pursuing this?
Just grabbing the database files is *not* a supported backup method.
Here, for instance, is a quote from BOL 2005, sp_attach_db:
"The sp_attach_db stored procedure should only be executed on databases that were previously
detached from the database server by using an explicit sp_detach_db operation or on copied
databases."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:96023056-5D2F-4C3A-9E22-BA13C362F90F@.microsoft.com...
>I have a batch process that stops the services. I tried the
> sp_attach_single_file_db and does not work. It seems that I must have the
> transaction file.
> "Hilary Cotter" wrote:
>> Are you doing an attach single file? This should work.Try using
>> sp_attach_single_file_db.
>> If you still get this error I would question how you are shutting down the
>> SQL Server? Perhaps you are using the kill command from a command prompt or
>> task manager.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
>> > Each night our SQL server is stopped and .mdf files are copied to a backup
>> > server. When I try to reattach the files it claims that they were not
>> > properly shut down. If I add the transaction file all is well. I was
>> > informed
>> > by an expert at Microsoft that this should not be happening. Any ideas why
>> > I
>> > am not getting a clean shutdown. The SQL server is stopped at night when
>> > there is not activity. TIA
>>|||I am stopping now. I will do an online backup from this point on. Thanks.
"Tibor Karaszi" wrote:
> I just have to ask: Why are you pursuing this?
> Just grabbing the database files is *not* a supported backup method.
> Here, for instance, is a quote from BOL 2005, sp_attach_db:
> "The sp_attach_db stored procedure should only be executed on databases that were previously
> detached from the database server by using an explicit sp_detach_db operation or on copied
> databases."
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:96023056-5D2F-4C3A-9E22-BA13C362F90F@.microsoft.com...
> >I have a batch process that stops the services. I tried the
> > sp_attach_single_file_db and does not work. It seems that I must have the
> > transaction file.
> >
> > "Hilary Cotter" wrote:
> >
> >> Are you doing an attach single file? This should work.Try using
> >> sp_attach_single_file_db.
> >>
> >> If you still get this error I would question how you are shutting down the
> >> SQL Server? Perhaps you are using the kill command from a command prompt or
> >> task manager.
> >>
> >> --
> >> Hilary Cotter
> >> Director of Text Mining and Database Strategy
> >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >>
> >> This posting is my own and doesn't necessarily represent RelevantNoise's
> >> positions, strategies or opinions.
> >>
> >> Looking for a SQL Server replication book?
> >> http://www.nwsu.com/0974973602.html
> >>
> >> Looking for a FAQ on Indexing Services/SQL FTS
> >> http://www.indexserverfaq.com
> >>
> >>
> >>
> >> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> >> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
> >> > Each night our SQL server is stopped and .mdf files are copied to a backup
> >> > server. When I try to reattach the files it claims that they were not
> >> > properly shut down. If I add the transaction file all is well. I was
> >> > informed
> >> > by an expert at Microsoft that this should not be happening. Any ideas why
> >> > I
> >> > am not getting a clean shutdown. The SQL server is stopped at night when
> >> > there is not activity. TIA
> >>
> >>
> >>
>

.mdf not properly shut down.

.mdf files

What are .mdf files and how do I use or open them? I have two .mdf files, but have no idea how to open them.

SQL Server creates an .mdf and an .ldf file for the database and the associated log. If you have both of them, you can attach them to your server. For more info, open up Books Online and read about attaching a database.

Thanks,
Sam Lester (MSFT)

|||In addition, if you don′t have any ldf file you can try to let SQL Server create a new one for you during attaching the file with sp_attach_single_file_db. If SQL Serve dcan reconstruct the concistency of the database without the ldf file it can create a new one. (Not guranteed that you will have logical concistency across your database)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I don't have an .ldf file and want to access the .mdf file. How do I attach it to the database? I don't have SQL Server. What should I do?

.mdf files

Hi,
I have 3 .mdf files on my system.
database.mdf (2 MB), database_1.mdf (450 MB), database_2.mdf (750 MB)
what does each file represent. If i have to take the backup of my
data(loga not required) then which file should i take.
Thanks
> what does each file represent. If i have to take the backup of my
> data(loga not required) then which file should i take.
Each file contains a portion of the database. You should backup the
database with BACKUP DATABASE rather than a file system backup. This will
backup all necessary data, including the transaction log, at consistent
state so that you can later RESTORE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gaurav" <gauravgoyal121@.gmail.com> wrote in message
news:1184326702.252930.117950@.n2g2000hse.googlegro ups.com...
> Hi,
> I have 3 .mdf files on my system.
> database.mdf (2 MB), database_1.mdf (450 MB), database_2.mdf (750 MB)
> what does each file represent. If i have to take the backup of my
> data(loga not required) then which file should i take.
> Thanks
>

.mdf files

What are .mdf files and how do I use or open them? I have two .mdf files, but have no idea how to open them. Thanks

Hello,

SQL Server databases comprise of at least one *.mdf (data) and one *.ldf (log) file.

In order to "utilise" the *.mdf, you'll need attach them to a db within SQL Server via the sp_attach_single_file_db procedure (as you state that you have only the mdf files) as such (the corresponding ldf (log) file will be created):

exec sp_attach_single_file_db 'NewDBNameHere', 'C:\MyMdfFileHere.mdf'

Cheers,

Rob

|||Thanks. I have another question then. Will the SQL Server Express be okay? If so, how?

.mdf files

What are .mdf files and how do I use or open them? I have two .mdf files, but have no idea how to open them.

Thanks,
Sam Lester (MSFT)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I don't have an .ldf file and want to access the .mdf file. How do I attach it to the database? I don't have SQL Server. What should I do?

.mdf files

Hi,
I have 3 .mdf files on my system.
database.mdf (2 MB), database_1.mdf (450 MB), database_2.mdf (750 MB)
what does each file represent. If i have to take the backup of my
data(loga not required) then which file should i take.
Thanks> what does each file represent. If i have to take the backup of my
> data(loga not required) then which file should i take.
Each file contains a portion of the database. You should backup the
database with BACKUP DATABASE rather than a file system backup. This will
backup all necessary data, including the transaction log, at consistent
state so that you can later RESTORE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gaurav" <gauravgoyal121@.gmail.com> wrote in message
news:1184326702.252930.117950@.n2g2000hse.googlegroups.com...
> Hi,
> I have 3 .mdf files on my system.
> database.mdf (2 MB), database_1.mdf (450 MB), database_2.mdf (750 MB)
> what does each file represent. If i have to take the backup of my
> data(loga not required) then which file should i take.
> Thanks
>

.mdf files

Hi,
I have 3 .mdf files on my system.
database.mdf (2 MB), database_1.mdf (450 MB), database_2.mdf (750 MB)
what does each file represent. If i have to take the backup of my
data(loga not required) then which file should i take.
Thanks> what does each file represent. If i have to take the backup of my
> data(loga not required) then which file should i take.
Each file contains a portion of the database. You should backup the
database with BACKUP DATABASE rather than a file system backup. This will
backup all necessary data, including the transaction log, at consistent
state so that you can later RESTORE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gaurav" <gauravgoyal121@.gmail.com> wrote in message
news:1184326702.252930.117950@.n2g2000hse.googlegroups.com...
> Hi,
> I have 3 .mdf files on my system.
> database.mdf (2 MB), database_1.mdf (450 MB), database_2.mdf (750 MB)
> what does each file represent. If i have to take the backup of my
> data(loga not required) then which file should i take.
> Thanks
>

.mdf file does not grow.

Hi,
I'm using msde as the database server for my asp.net intranet
application. The application is running live and atleast 50 - 100
records get inserted everyday.
However I have been checking the .mdf file everyday for the last week
and surprisingly the file size is constant at 5,504 KB. and the log
file is 504 KB. I ran the sp_spaceused stored procedure which gave me
similar results, its shows database size as 6.13 MB but it has been
constant for last few days too.
Is this normal? Is the growth in the size of the data too minor to
reflect in the file size? Or am I missing something here? Its important
for me to monitor the growth in database size for maintenance purposes.
So any help on this topic will be appreciated.
Regards,
supzzz
Do those records inserted stay in the database? If yes, why worry the file
size?
SQL Server's database file/log size increase by predetermined size chunck (%
or MB, say 10%, or 10MB).
"supriya" <supzzz@.gmail.com> wrote in message
news:1162460873.660306.113530@.b28g2000cwb.googlegr oups.com...
> Hi,
> I'm using msde as the database server for my asp.net intranet
> application. The application is running live and atleast 50 - 100
> records get inserted everyday.
> However I have been checking the .mdf file everyday for the last week
> and surprisingly the file size is constant at 5,504 KB. and the log
> file is 504 KB. I ran the sp_spaceused stored procedure which gave me
> similar results, its shows database size as 6.13 MB but it has been
> constant for last few days too.
> Is this normal? Is the growth in the size of the data too minor to
> reflect in the file size? Or am I missing something here? Its important
> for me to monitor the growth in database size for maintenance purposes.
> So any help on this topic will be appreciated.
> Regards,
> supzzz
>
|||A database file is different that a normal file system file. You create a
database file with a particular size and SQL Server allocates space within
the file for tables, rows, etc. This is more like a file-system partition
than a normal file. You create a partition on a disk and the OS allocates
space within the partition as the files require it. The partition itself
doesn't grow as file are added.
When a file system partition runs out of space, you can't put any more files
into it. Many database work this way - when they run out of their allocated
space, insert operations start failing because there is no place to put the
new rows. SQL Server has an option that will automatically expand the size
of the database when it runs out of room. Expanding the database is a
pretty expensive operation so instead of growing just enough to make room
for the row you are adding, it grows enough to make room for a number of
rows. You can set how much it grows each time it runs out of space.
Just as a Windows partition doesn't shrink when you remove files, a database
file doesn't shrink when you delete rows. SQL Server assumes that you are
going to add more rows so it just keeps the space around so it doesn't have
to grow when you add more data.
You can see this happening if you watch the mdf file size over a period of
time. It won't grow for a while and then the size will jump by a
significant amount and then it won't grow for a while again. You can delete
all the tables in the database and the mdf file size won't shrink. If you
really need the disk space and you know you aren't going to add more data to
the database, you can shrink the file but in general, it's best to just
leave the file at its current size because you'll probably need the space
eventually.
When SQL Server runs out of space when trying to insert data, the insert
command is suspended while the database file size is increased. It's
possible for this to take a minute or more so the program that is doing the
insert will appear to hang. This isn't a good user experience so a lot of
DBA's monitor the free space and manually increase the database size when it
is getting low. This is more work for the DBA but it makes the user's
experience better (and also prevents the call to the DBA in the middle of
the night because an application appears to be hung while SQL Server is
growing the database file).
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"supriya" <supzzz@.gmail.com> wrote in message
news:1162460873.660306.113530@.b28g2000cwb.googlegr oups.com...
> Hi,
> I'm using msde as the database server for my asp.net intranet
> application. The application is running live and atleast 50 - 100
> records get inserted everyday.
> However I have been checking the .mdf file everyday for the last week
> and surprisingly the file size is constant at 5,504 KB. and the log
> file is 504 KB. I ran the sp_spaceused stored procedure which gave me
> similar results, its shows database size as 6.13 MB but it has been
> constant for last few days too.
> Is this normal? Is the growth in the size of the data too minor to
> reflect in the file size? Or am I missing something here? Its important
> for me to monitor the growth in database size for maintenance purposes.
> So any help on this topic will be appreciated.
> Regards,
> supzzz
>
|||Thank You for your replies. Roger, your reply was particularly
insightful.
Ok so I guess there is not much reason to sweat. I just wanted to
monitor to activity on the database in order to estimate how soon we'll
have to switch to SQL Server 2000 (thanks to the 2GB limit) and plan
the purge procedure accordingly!!
BTW, while we are at it... is there a way to check the database
properties from osql? E.g. grow database automatically, grow log
automatically etc.
Thanks & Regards,
supzz
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> A database file is different that a normal file system file. You create a
> database file with a particular size and SQL Server allocates space within
> the file for tables, rows, etc. This is more like a file-system partition
> than a normal file. You create a partition on a disk and the OS allocates
> space within the partition as the files require it. The partition itself
> doesn't grow as file are added.
> When a file system partition runs out of space, you can't put any more files
> into it. Many database work this way - when they run out of their allocated
> space, insert operations start failing because there is no place to put the
> new rows. SQL Server has an option that will automatically expand the size
> of the database when it runs out of room. Expanding the database is a
> pretty expensive operation so instead of growing just enough to make room
> for the row you are adding, it grows enough to make room for a number of
> rows. You can set how much it grows each time it runs out of space.
> Just as a Windows partition doesn't shrink when you remove files, a database
> file doesn't shrink when you delete rows. SQL Server assumes that you are
> going to add more rows so it just keeps the space around so it doesn't have
> to grow when you add more data.
> You can see this happening if you watch the mdf file size over a period of
> time. It won't grow for a while and then the size will jump by a
> significant amount and then it won't grow for a while again. You can delete
> all the tables in the database and the mdf file size won't shrink. If you
> really need the disk space and you know you aren't going to add more data to
> the database, you can shrink the file but in general, it's best to just
> leave the file at its current size because you'll probably need the space
> eventually.
> When SQL Server runs out of space when trying to insert data, the insert
> command is suspended while the database file size is increased. It's
> possible for this to take a minute or more so the program that is doing the
> insert will appear to hang. This isn't a good user experience so a lot of
> DBA's monitor the free space and manually increase the database size when it
> is getting low. This is more work for the DBA but it makes the user's
> experience better (and also prevents the call to the DBA in the middle of
> the night because an application appears to be hung while SQL Server is
> growing the database file).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "supriya" <supzzz@.gmail.com> wrote in message
> news:1162460873.660306.113530@.b28g2000cwb.googlegr oups.com...

Saturday, February 25, 2012

.NET 1.xxx and .NET 2.0 beta for SQLRS

.net 1.1 SqlDbType accepts varchar(MAX)

.NET 1.1 running on SQL Server 2005

.net 1.1 and SQL Server 2005

.NET 1.1 and .NET 2 Perceives DB data differently

.NET /script/T-SQL Code to deliberately cause Cluster Failover

.Net <-> SqlServer admin actions

.neRe: Store procedures & Functions

.ndf

.mdf/.ldf on a NAS

.MDF with MIcrosoft Excel.........

.mdf to .mdb

.mdf to .mdb

.MDF recovery after data failure

.MDF recovery after data failure

.MDF recovery after data failure

.mdf not properly shut down.

.mdf not properly shut down.

.mdf not properly shut down.

.mdf files

.mdf files

.mdf files

.mdf files

.mdf files

.mdf files

.mdf file does not grow.

SQL SQLExpress

Blog Archive

About Me