Attach remote DB file: files

Showing posts with label files. Show all posts

Tuesday, March 27, 2012

Auto delete of backup files after 1 week problem

I have SQL 2000 maintenance plan setup to do full backups and have the "delete backups after 1 week" enabled but it never works. It seems to only work on the master and msdb backups but no user databases.

Any ideas?
Do you have any antivirus software installed?
What is the service pack on SQL Server?|||I think we have Trend Server Protect on all the servers.

How do I check the service pack of SQL?

Thursday, March 22, 2012

Authentication permissions

Hi,
How do I change RSs authentication from Windows to SQL Server only? What
config files need to be changed?
What else needs to be changed? I just want authentication at the SQL Server
level only.
If I can't how about using Forms authentication instead for RS. What do I
need to change for this to happen?
Thanks,
JJ> How do I change RSs authentication from Windows to SQL Server only?
Can you explain what you mean here?...[If you mean the credentials RS needs
to connect to the SQL server and execute the report you can set SQL Srv up
to use SQL Server authentication, set up a user(s) that has access to your
database, and in the shared data source choose the 'Connect Using' option
you want.]
> If I can't how about using Forms authentication instead for RS. What do I
> need to change for this to happen?
Here's a white paper that can get you started:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
Adrian M.
MCP
"JJ" <jjmraz@.-NOspAM*hotmail.com> wrote in message
news:%23s$77MIBFHA.2112@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I change RSs authentication from Windows to SQL Server only? What
> config files need to be changed?
> What else needs to be changed? I just want authentication at the SQL
> Server
> level only.
>
> Thanks,
> JJ
>|||Adrian,
What Connect Using option is appropriate when the SQL Server user name and
password are specified in the connection string?
That is, using SQL Authentication to connect from RS to the datasource. I
want an option that says SQL Authentication in addition to all the windows
authentication options already there.
Seems a lot of people are confused about this. My user, report services and
data source SQL server are on three different computers, and I don't have
kerberos installed.
Thanks
Martin
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:OoSThJKBFHA.2112@.TK2MSFTNGP14.phx.gbl...
>> How do I change RSs authentication from Windows to SQL Server only?
> Can you explain what you mean here?...[If you mean the credentials RS
> needs to connect to the SQL server and execute the report you can set SQL
> Srv up to use SQL Server authentication, set up a user(s) that has access
> to your database, and in the shared data source choose the 'Connect Using'
> option you want.]
>> If I can't how about using Forms authentication instead for RS. What do I
>> need to change for this to happen?
> Here's a white paper that can get you started:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
>
> --
> Adrian M.
> MCP
>|||Looks like No Credentials is the appropriate option when passing sql auth
credentials in connection string.
Martin
"Martin" <x@.y.z> wrote in message
news:efIbvyWIFHA.3108@.tk2msftngp13.phx.gbl...
> Adrian,
> What Connect Using option is appropriate when the SQL Server user name and
> password are specified in the connection string?
> That is, using SQL Authentication to connect from RS to the datasource. I
> want an option that says SQL Authentication in addition to all the windows
> authentication options already there.
> Seems a lot of people are confused about this. My user, report services
> and data source SQL server are on three different computers, and I don't
> have kerberos installed.
> Thanks
> Martin
> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
> news:OoSThJKBFHA.2112@.TK2MSFTNGP14.phx.gbl...
>> How do I change RSs authentication from Windows to SQL Server only?
>> Can you explain what you mean here?...[If you mean the credentials RS
>> needs to connect to the SQL server and execute the report you can set SQL
>> Srv up to use SQL Server authentication, set up a user(s) that has access
>> to your database, and in the shared data source choose the 'Connect
>> Using' option you want.]
>> If I can't how about using Forms authentication instead for RS. What do
>> I
>> need to change for this to happen?
>> Here's a white paper that can get you started:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
>>
>> --
>> Adrian M.
>> MCP
>|||I actually don't pass the SQL auth credentials in the connect string. I use
the 'Credentials Stored Securely in the Report Server' option. That's
because most of my reports have schedule subscriptions. I haven't had an
issue with this setup.
--
Adrian M.
MCP
"Martin" <x@.y.z> wrote in message
news:uLbKntXIFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Looks like No Credentials is the appropriate option when passing sql auth
> credentials in connection string.
> Martin
> "Martin" <x@.y.z> wrote in message
> news:efIbvyWIFHA.3108@.tk2msftngp13.phx.gbl...
>> Adrian,
>> What Connect Using option is appropriate when the SQL Server user name
>> and password are specified in the connection string?
>> That is, using SQL Authentication to connect from RS to the datasource.
>> I want an option that says SQL Authentication in addition to all the
>> windows authentication options already there.
>> Seems a lot of people are confused about this. My user, report services
>> and data source SQL server are on three different computers, and I don't
>> have kerberos installed.
>> Thanks
>> Martin
>> "Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
>> news:OoSThJKBFHA.2112@.TK2MSFTNGP14.phx.gbl...
>> How do I change RSs authentication from Windows to SQL Server only?
>> Can you explain what you mean here?...[If you mean the credentials RS
>> needs to connect to the SQL server and execute the report you can set
>> SQL Srv up to use SQL Server authentication, set up a user(s) that has
>> access to your database, and in the shared data source choose the
>> 'Connect Using' option you want.]
>> If I can't how about using Forms authentication instead for RS. What do
>> I
>> need to change for this to happen?
>> Here's a white paper that can get you started:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
>>
>> --
>> Adrian M.
>> MCP
>>
>

Thursday, March 8, 2012

Audit Trace Files

I am using server side traces to enable SQL Server 2000 and 2005 auditing.
Every time I start the server, I define a new trace file for auditing. So
till now I have configured around 100 trace files.
Now I want to get the list of all the trace files (which are at different
location in my PC), using some mechanism. Please let me know if there is a
function or procedure which will give me all the trace files that have been
configured for auditing in SQL 2000 and 2005.
I found a function fn_trace_getinfo, which returns information about a
specified trace or all existing traces, but I want all the trace files, whic
h
have been created till now, irrespective of whether they are active or not.
Is there a function in SQL which would help me in this?Munish Narula (munish.narula@.wipro.com) writes:
> I am using server side traces to enable SQL Server 2000 and 2005 auditing.
> Every time I start the server, I define a new trace file for auditing. So
> till now I have configured around 100 trace files.
> Now I want to get the list of all the trace files (which are at
> different location in my PC), using some mechanism. Please let me know
> if there is a function or procedure which will give me all the trace
> files that have been configured for auditing in SQL 2000 and 2005.
> I found a function fn_trace_getinfo, which returns information about a
> specified trace or all existing traces, but I want all the trace files,
> which have been created till now, irrespective of whether they are
> active or not.
> Is there a function in SQL which would help me in this?
I don't think so. As I understand it, once a trace is done, SQL Server
forgets all about it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
In SQL Server 2005 take a look at sys.traces system table
"Munish Narula" <munish.narula@.wipro.com> wrote in message
news:4412799B-EE0B-431B-92E2-F51988471166@.microsoft.com...
>I am using server side traces to enable SQL Server 2000 and 2005 auditing.
> Every time I start the server, I define a new trace file for auditing. So
> till now I have configured around 100 trace files.
> Now I want to get the list of all the trace files (which are at different
> location in my PC), using some mechanism. Please let me know if there is a
> function or procedure which will give me all the trace files that have
> been
> configured for auditing in SQL 2000 and 2005.
> I found a function fn_trace_getinfo, which returns information about a
> specified trace or all existing traces, but I want all the trace files,
> which
> have been created till now, irrespective of whether they are active or
> not.
> Is there a function in SQL which would help me in this?|||Hi,
Thanks for the response.
But this again does not solve my problem. The sys.traces catalog view
contains the current running traces on the system. This view is intended as
a
replacement for the fn_trace_getinfo function.
I want to get all the trace files that have been created so far.
"Uri Dimant" wrote:

> Hi
> In SQL Server 2005 take a look at sys.traces system table
>
>
> "Munish Narula" <munish.narula@.wipro.com> wrote in message
> news:4412799B-EE0B-431B-92E2-F51988471166@.microsoft.com...
>
>|||Munish
I'm affraid you cannot. Once trace is stopped SQL Server does not keep any
info about it
"Munish Narula" <munish.narula@.wipro.com> wrote in message
news:07439C49-276C-4677-B975-0341882D2C0F@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for the response.
> But this again does not solve my problem. The sys.traces catalog view
> contains the current running traces on the system. This view is intended
> as a
> replacement for the fn_trace_getinfo function.
> I want to get all the trace files that have been created so far.
>
>
> "Uri Dimant" wrote:
>|||You can get the file path only for active and stopped traces. As Erland and
Uri mentioned, no record is kept after the trace is deleted.
It's not hard to develop a process to manage trace files, though. For
example, I have a daily SSIS package that restarts a trace and copies the
files to an archive folder.
Hope this helps.
Dan Guzman
SQL Server MVP
"Munish Narula" <munish.narula@.wipro.com> wrote in message
news:4412799B-EE0B-431B-92E2-F51988471166@.microsoft.com...
>I am using server side traces to enable SQL Server 2000 and 2005 auditing.
> Every time I start the server, I define a new trace file for auditing. So
> till now I have configured around 100 trace files.
> Now I want to get the list of all the trace files (which are at different
> location in my PC), using some mechanism. Please let me know if there is a
> function or procedure which will give me all the trace files that have
> been
> configured for auditing in SQL 2000 and 2005.
> I found a function fn_trace_getinfo, which returns information about a
> specified trace or all existing traces, but I want all the trace files,
> which
> have been created till now, irrespective of whether they are active or
> not.
> Is there a function in SQL which would help me in this?

Wednesday, March 7, 2012

Audit Log

Hello,
I wanted a query regarding archiving the data from the log files. Really wha
t I wanted here is as follows:
When we change the data through the application, we can have the log to see
who has changed what data. But when the data is modified through the SQL
Server Enterprise Manager or Query Analyzer we can not have the log. But I
think this will be recorded in the log files. But how can I archive this
data. Means can I write some queries or stored procedures to get the
modifications done in the database?
--With Regards,
Sheshadrinath.Ran UPDATE trigger would be one way.
"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:8F77796A-071B-4609-8E0E-F0275E2A7434@.microsoft.com...
> Hello,
> I wanted a query regarding archiving the data from the log files. Really
> wha
> t I wanted here is as follows:
> When we change the data through the application, we can have the log to
> see
> who has changed what data. But when the data is modified through the SQL
> Server Enterprise Manager or Query Analyzer we can not have the log. But I
> think this will be recorded in the log files. But how can I archive this
> data. Means can I write some queries or stored procedures to get the
> modifications done in the database?
> --With Regards,
> Sheshadrinath.R|||Hi,
true said you can do it with INSERTED AND UPDATED TABLE , please go through
the article on the same topic as you ask at :
http://www.netdscure.co.in/Articles/AuditDML.htm
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Sheshadrinath R" wrote:

> Hello,
> I wanted a query regarding archiving the data from the log files. Really w
ha
> t I wanted here is as follows:
> When we change the data through the application, we can have the log to se
e
> who has changed what data. But when the data is modified through the SQL
> Server Enterprise Manager or Query Analyzer we can not have the log. But I
> think this will be recorded in the log files. But how can I archive this
> data. Means can I write some queries or stored procedures to get the
> modifications done in the database?
> --With Regards,
> Sheshadrinath.R

Saturday, February 25, 2012

Attribute- vs. Element-Centric XML

Hi all,
I am working on a project that involves extracting the data from XML files i
nto SQL Server 2000. My dilemma is attribute- vs. element-centric XML files.
When to use one over the other, and why?
References to good websites would be helpfull, too.
Thanks
Goran DjuranovicGoran Djuranovic wrote:
> Hi all,
> I am working on a project that involves extracting the data from XML
> files into SQL Server 2000. My dilemma is attribute- vs. element-centric
> XML files.
> When to use one over the other, and why?
> References to good websites would be helpfull, too.
This is a FAQ: see http://xml.silmaril.ie/developers/attributes/
///Peter|||Hi Goran,
In addition to the faq article Peter has provided, I think whether to
choose Attribute or Element centric XML format also depend on your
application layer's design or requirement. And such XML format is usually
important when we do data exporting from SQL Server, because when exporting
data, the exported XML document will be further used by other application
or data processing component which may have particular requirement on the
XML document. As for importing ,this won't matter much as long as the
format is recognizable by the SQL Server engine.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks, both.
Goran
"Goran Djuranovic" <goran.djuranovic@.newsgroups.nospam> wrote in message new
s:u4l7QdafGHA.356@.TK2MSFTNGP02.phx.gbl...
Hi all,
I am working on a project that involves extracting the data from XML files i
nto SQL Server 2000. My dilemma is attribute- vs. element-centric XML files.
When to use one over the other, and why?
References to good websites would be helpfull, too.
Thanks
Goran Djuranovic

Friday, February 24, 2012

Attempted to move System table

Sunday, February 19, 2012

Attempted to move System table

I'm trying to move the System tables off to my array that contains the DB
tables and the array that contains the Log files respectively.
For the modeldev and modellog I've issued the command:
ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'E:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
Stopped the SQL Server service, and moved the tables to respective locations.
Now the SQL Server service will not start. I get the following error in the
EVT Log:
1)
FCB::Open failed: Could not open file D:\Microsoft SQL
Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is denied.).
2)
FCB::Open: Operating system error 5(Access is denied.) occurred while
creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
Diagnose and correct the operating system error, and retry the operation.
3)
FCB::Open failed: Could not open file E:\Microsoft SQL
Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is denied.).
4)
FCB::Open: Operating system error 5(Access is denied.) occurred while
creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
Diagnose and correct the operating system error, and retry the operation.
I cannot restart the service to fix it back to the original location....I'm
stuck. Thanks!Needed to add:
I check the rights on both of those dir's, the Service Account that was
assigned to SQL at install has "Full Control" on both dir's.
TIA.
"Scott" wrote:
> I'm trying to move the System tables off to my array that contains the DB
> tables and the array that contains the Log files respectively.
> For the modeldev and modellog I've issued the command:
> ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'E:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> Stopped the SQL Server service, and moved the tables to respective locations.
> Now the SQL Server service will not start. I get the following error in the
> EVT Log:
> 1)
> FCB::Open failed: Could not open file D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is denied.).
> 2)
> FCB::Open: Operating system error 5(Access is denied.) occurred while
> creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> Diagnose and correct the operating system error, and retry the operation.
> 3)
> FCB::Open failed: Could not open file E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is denied.).
> 4)
> FCB::Open: Operating system error 5(Access is denied.) occurred while
> creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> Diagnose and correct the operating system error, and retry the operation.
> I cannot restart the service to fix it back to the original location....I'm
> stuck. Thanks!|||http://support.microsoft.com/kb/224071/en-us
I would recommend that you not use the -m switch however.
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:C611977F-D987-4FD9-81BD-60D5B12013EC@.microsoft.com...
> I'm trying to move the System tables off to my array that contains the DB
> tables and the array that contains the Log files respectively.
> For the modeldev and modellog I've issued the command:
> ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME => 'D:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME => 'E:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> Stopped the SQL Server service, and moved the tables to respective
> locations.
> Now the SQL Server service will not start. I get the following error in
> the
> EVT Log:
> 1)
> FCB::Open failed: Could not open file D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is
> denied.).
> 2)
> FCB::Open: Operating system error 5(Access is denied.) occurred while
> creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> Diagnose and correct the operating system error, and retry the operation.
> 3)
> FCB::Open failed: Could not open file E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is
> denied.).
> 4)
> FCB::Open: Operating system error 5(Access is denied.) occurred while
> creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> Diagnose and correct the operating system error, and retry the operation.
> I cannot restart the service to fix it back to the original
> location....I'm
> stuck. Thanks!|||Jay,
Thanks for the link. However, this doesn't provide instructions on how to
recover from the fact that the SQL Server service will not start now.
Scott
"Jay" wrote:
> http://support.microsoft.com/kb/224071/en-us
> I would recommend that you not use the -m switch however.
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:C611977F-D987-4FD9-81BD-60D5B12013EC@.microsoft.com...
> > I'm trying to move the System tables off to my array that contains the DB
> > tables and the array that contains the Log files respectively.
> >
> > For the modeldev and modellog I've issued the command:
> > ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME => > 'D:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> > ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME => > 'E:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> >
> > Stopped the SQL Server service, and moved the tables to respective
> > locations.
> >
> > Now the SQL Server service will not start. I get the following error in
> > the
> > EVT Log:
> > 1)
> > FCB::Open failed: Could not open file D:\Microsoft SQL
> > Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is
> > denied.).
> >
> > 2)
> > FCB::Open: Operating system error 5(Access is denied.) occurred while
> > creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> > Diagnose and correct the operating system error, and retry the operation.
> >
> > 3)
> > FCB::Open failed: Could not open file E:\Microsoft SQL
> > Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is
> > denied.).
> >
> > 4)
> > FCB::Open: Operating system error 5(Access is denied.) occurred while
> > creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> > Diagnose and correct the operating system error, and retry the operation.
> >
> > I cannot restart the service to fix it back to the original
> > location....I'm
> > stuck. Thanks!
>
>|||So I was able to login via the -T3608 switch but I'm still getting the
following error:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlcmd -q "ALTER
DATAB
SE model MODIFY FILE (Name = modeldev, FILENAME = 'E:\Microsoft SQL
Server\MSSQ
.1\MSSQL\Data\Model.mdf')"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
establ
shing a connection to the server. When connecting to SQL Server 2005, this
fail
re may be caused by the fact that under the default settings SQL Server does
no
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
"Jay" wrote:
> http://support.microsoft.com/kb/224071/en-us
> I would recommend that you not use the -m switch however.
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:C611977F-D987-4FD9-81BD-60D5B12013EC@.microsoft.com...
> > I'm trying to move the System tables off to my array that contains the DB
> > tables and the array that contains the Log files respectively.
> >
> > For the modeldev and modellog I've issued the command:
> > ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME => > 'D:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> > ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME => > 'E:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> >
> > Stopped the SQL Server service, and moved the tables to respective
> > locations.
> >
> > Now the SQL Server service will not start. I get the following error in
> > the
> > EVT Log:
> > 1)
> > FCB::Open failed: Could not open file D:\Microsoft SQL
> > Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is
> > denied.).
> >
> > 2)
> > FCB::Open: Operating system error 5(Access is denied.) occurred while
> > creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> > Diagnose and correct the operating system error, and retry the operation.
> >
> > 3)
> > FCB::Open failed: Could not open file E:\Microsoft SQL
> > Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is
> > denied.).
> >
> > 4)
> > FCB::Open: Operating system error 5(Access is denied.) occurred while
> > creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> > Diagnose and correct the operating system error, and retry the operation.
> >
> > I cannot restart the service to fix it back to the original
> > location....I'm
> > stuck. Thanks!
>
>|||I think you're going to have to modify the registry directly.
Personally, I hate regedit as you can do a tremendous amount of damage.
Because of that, I won't tell you what I do know, just to go to
http://support.microsoft.com/, search on HKEY_LOCAL_MACHINE, refine the
search to your version of SQL server and select the best kb title you find.
This way, you are 100% responsible.
Good luck,
Jay
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D366AF2E-C6D4-43AE-A200-FD3C647D02CA@.microsoft.com...
> So I was able to login via the -T3608 switch but I'm still getting the
> following error:
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlcmd -q "ALTER
> DATAB
> SE model MODIFY FILE (Name = modeldev, FILENAME = 'E:\Microsoft SQL
> Server\MSSQ
> .1\MSSQL\Data\Model.mdf')"
> HResult 0x2, Level 16, State 1
> Named Pipes Provider: Could not open a connection to SQL Server [2].
> Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> establ
> shing a connection to the server. When connecting to SQL Server 2005, this
> fail
> re may be caused by the fact that under the default settings SQL Server
> does
> no
> allow remote connections..
> Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
>
> "Jay" wrote:
>> http://support.microsoft.com/kb/224071/en-us
>> I would recommend that you not use the -m switch however.
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:C611977F-D987-4FD9-81BD-60D5B12013EC@.microsoft.com...
>> > I'm trying to move the System tables off to my array that contains the
>> > DB
>> > tables and the array that contains the Log files respectively.
>> >
>> > For the modeldev and modellog I've issued the command:
>> > ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME =>> > 'D:\Program
>> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
>> > ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME =>> > 'E:\Program
>> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
>> >
>> > Stopped the SQL Server service, and moved the tables to respective
>> > locations.
>> >
>> > Now the SQL Server service will not start. I get the following error in
>> > the
>> > EVT Log:
>> > 1)
>> > FCB::Open failed: Could not open file D:\Microsoft SQL
>> > Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is
>> > denied.).
>> >
>> > 2)
>> > FCB::Open: Operating system error 5(Access is denied.) occurred while
>> > creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
>> > Diagnose and correct the operating system error, and retry the
>> > operation.
>> >
>> > 3)
>> > FCB::Open failed: Could not open file E:\Microsoft SQL
>> > Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is
>> > denied.).
>> >
>> > 4)
>> > FCB::Open: Operating system error 5(Access is denied.) occurred while
>> > creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
>> > Diagnose and correct the operating system error, and retry the
>> > operation.
>> >
>> > I cannot restart the service to fix it back to the original
>> > location....I'm
>> > stuck. Thanks!
>>|||Thanks for the info Jay. It's a test server, so no real damage if I need to
reinstall.
Thanks again.
"Jay" wrote:
> I think you're going to have to modify the registry directly.
> Personally, I hate regedit as you can do a tremendous amount of damage.
> Because of that, I won't tell you what I do know, just to go to
> http://support.microsoft.com/, search on HKEY_LOCAL_MACHINE, refine the
> search to your version of SQL server and select the best kb title you find.
> This way, you are 100% responsible.
> Good luck,
> Jay
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D366AF2E-C6D4-43AE-A200-FD3C647D02CA@.microsoft.com...
> > So I was able to login via the -T3608 switch but I'm still getting the
> > following error:
> >
> > C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlcmd -q "ALTER
> > DATAB
> > SE model MODIFY FILE (Name = modeldev, FILENAME = 'E:\Microsoft SQL
> > Server\MSSQ
> > .1\MSSQL\Data\Model.mdf')"
> > HResult 0x2, Level 16, State 1
> > Named Pipes Provider: Could not open a connection to SQL Server [2].
> > Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while
> > establ
> > shing a connection to the server. When connecting to SQL Server 2005, this
> > fail
> > re may be caused by the fact that under the default settings SQL Server
> > does
> > no
> > allow remote connections..
> > Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
> >
> >
> > "Jay" wrote:
> >
> >> http://support.microsoft.com/kb/224071/en-us
> >>
> >> I would recommend that you not use the -m switch however.
> >>
> >> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> >> news:C611977F-D987-4FD9-81BD-60D5B12013EC@.microsoft.com...
> >> > I'm trying to move the System tables off to my array that contains the
> >> > DB
> >> > tables and the array that contains the Log files respectively.
> >> >
> >> > For the modeldev and modellog I've issued the command:
> >> > ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME => >> > 'D:\Program
> >> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> >> > ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME => >> > 'E:\Program
> >> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' )
> >> >
> >> > Stopped the SQL Server service, and moved the tables to respective
> >> > locations.
> >> >
> >> > Now the SQL Server service will not start. I get the following error in
> >> > the
> >> > EVT Log:
> >> > 1)
> >> > FCB::Open failed: Could not open file D:\Microsoft SQL
> >> > Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is
> >> > denied.).
> >> >
> >> > 2)
> >> > FCB::Open: Operating system error 5(Access is denied.) occurred while
> >> > creating or opening file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> >> > Diagnose and correct the operating system error, and retry the
> >> > operation.
> >> >
> >> > 3)
> >> > FCB::Open failed: Could not open file E:\Microsoft SQL
> >> > Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is
> >> > denied.).
> >> >
> >> > 4)
> >> > FCB::Open: Operating system error 5(Access is denied.) occurred while
> >> > creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'.
> >> > Diagnose and correct the operating system error, and retry the
> >> > operation.
> >> >
> >> > I cannot restart the service to fix it back to the original
> >> > location....I'm
> >> > stuck. Thanks!
> >>
> >>
> >>
>
>

Thursday, February 16, 2012

Attaching the log file and the mdf files of the database.

When I try to attach both the log file and mdf files

I got the following error message.

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'FCIT-GFOX082'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The header for file 'C:\Documents and Settings\Administrator\My Documents\Microsoft Press\AS2005SBS\Setup\Query\log_attach_databases.txt' is not a valid database file header. The FILE SIZE property is incorrect. (Microsoft SQL Server, Error: 5172)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=5172&LinkId=20476

BUTTONS:

Could someone help me on how to go about this.

Regards,

Ronald

Maybe you need to take a step back and tell us what you were trying to accomplish. SSAS does not use mdf and log files. And the file in the error message you posted is a .txt file.

SSAS either uses a .abf format for back up and restore, or an xmla script to create an empty database.

|||

I am trying to attach the SSAS Step by Step database that comes with the book Microsoft SQL SERVER 2005 Analysis SErvices Step by Step.

When I try it out, I get error messages.

Regards,

Ronaldl

Attaching SQL Server 2000 database on SQL Serve 2005

Hello!
We are planning to migrate our multi terabyte SQL Server 2000 database
by detaching data/log files on 2000 box and reattaching them on 2005. Has
anyone experienced any issues with this process in general and databases of
such size in particular? As far as I know, SQL Server will perform database
conversion when attaching database on SQL 2005.
Any thoughts are greatly appreciated,
IgorThis is a simple test,with a database names 'test' which on sqlserver 2000.
first:
Stop the database's servers(2000) on machine.
second:
Execute the command on 2005 server:
exec sp_attach_db @.dbname = N'test',
@.filename1 = N'd:\sqlserver database\MSSQL\data\test_Data.MDF',
@.filename2 = N'd:\sqlserver database\MSSQL\data\test_Log.LDF'
ok.So easy.And the server will return a message:'Converting database 'test'
from version 539 to the current version 607.'
"imarchenko" wrote:
> Hello!
> We are planning to migrate our multi terabyte SQL Server 2000 database
> by detaching data/log files on 2000 box and reattaching them on 2005. Has
> anyone experienced any issues with this process in general and databases of
> such size in particular? As far as I know, SQL Server will perform database
> conversion when attaching database on SQL 2005.
>
> Any thoughts are greatly appreciated,
> Igor
>
>|||you should copy the datafile to a specifically catalog ã'And use new files to
attach.Otherwise 2000 server's 'test' database will disappear
"luyan" wrote:
> This is a simple test,with a database names 'test' which on sqlserver 2000.
> first:
> Stop the database's servers(2000) on machine.
> second:
> Execute the command on 2005 server:
> exec sp_attach_db @.dbname = N'test',
> @.filename1 = N'd:\sqlserver database\MSSQL\data\test_Data.MDF',
> @.filename2 = N'd:\sqlserver database\MSSQL\data\test_Log.LDF'
> ok.So easy.And the server will return a message:'Converting database 'test'
> from version 539 to the current version 607.'
> "imarchenko" wrote:
> > Hello!
> >
> > We are planning to migrate our multi terabyte SQL Server 2000 database
> > by detaching data/log files on 2000 box and reattaching them on 2005. Has
> > anyone experienced any issues with this process in general and databases of
> > such size in particular? As far as I know, SQL Server will perform database
> > conversion when attaching database on SQL 2005.
> >
> >
> > Any thoughts are greatly appreciated,
> > Igor
> >
> >
> >|||luyan,
Thanks for you reply. I do know how to detach/attach database. My
question is whether anyone has experienced any problems while attaching
terabyte databases.
Thanks,
Igor
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:051EBA51-A12E-4E42-A07B-94B5D588DA72@.microsoft.com...
> you should copy the datafile to a specifically catalog ?And use new files
> to
> attach.Otherwise 2000 server's 'test' database will disappear
> "luyan" wrote:
>> This is a simple test,with a database names 'test' which on sqlserver
>> 2000.
>> first:
>> Stop the database's servers(2000) on machine.
>> second:
>> Execute the command on 2005 server:
>> exec sp_attach_db @.dbname = N'test',
>> @.filename1 = N'd:\sqlserver database\MSSQL\data\test_Data.MDF',
>> @.filename2 = N'd:\sqlserver database\MSSQL\data\test_Log.LDF'
>> ok.So easy.And the server will return a message:'Converting database
>> 'test'
>> from version 539 to the current version 607.'
>> "imarchenko" wrote:
>> > Hello!
>> >
>> > We are planning to migrate our multi terabyte SQL Server 2000
>> > database
>> > by detaching data/log files on 2000 box and reattaching them on 2005.
>> > Has
>> > anyone experienced any issues with this process in general and
>> > databases of
>> > such size in particular? As far as I know, SQL Server will perform
>> > database
>> > conversion when attaching database on SQL 2005.
>> >
>> >
>> > Any thoughts are greatly appreciated,
>> > Igor
>> >
>> >
>> >|||It may take a long time. I did restore a 500 MB SQL 2000 DB to SQL 2005 and
it took about 10 minutes on SAN system. In your situation you have to
consider this issue.
Bill
"imarchenko" wrote:
> Hello!
> We are planning to migrate our multi terabyte SQL Server 2000 database
> by detaching data/log files on 2000 box and reattaching them on 2005. Has
> anyone experienced any issues with this process in general and databases of
> such size in particular? As far as I know, SQL Server will perform database
> conversion when attaching database on SQL 2005.
>
> Any thoughts are greatly appreciated,
> Igor
>
>|||Bill,
Thanks for you feedback.
Igor
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:D23E2D1B-DAA2-4FA2-A5CA-E253DC148BED@.microsoft.com...
> It may take a long time. I did restore a 500 MB SQL 2000 DB to SQL 2005
> and
> it took about 10 minutes on SAN system. In your situation you have to
> consider this issue.
> Bill
> "imarchenko" wrote:
>> Hello!
>> We are planning to migrate our multi terabyte SQL Server 2000
>> database
>> by detaching data/log files on 2000 box and reattaching them on 2005. Has
>> anyone experienced any issues with this process in general and databases
>> of
>> such size in particular? As far as I know, SQL Server will perform
>> database
>> conversion when attaching database on SQL 2005.
>>
>> Any thoughts are greatly appreciated,
>> Igor
>>

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching MSDB database

Anyway to attach the MSDB database? I have the .mdf and .ldf files from
before a re-install. It won't allow me to detach MSDB as it says "System
databases cannot be detached". I would like to replace the current MSDB. I
don't have a database backup file.
Thanks.
RonRon,
Provided you really know that this is what you want, you can do the
following:
1 - Backup your existing msdb database.
2 - Shut down your SQL Server.
3 - Move the existing msdb .mdf and .ldf files to somewhere else.
4 - Copy your previous msdb .mdf and .ldf to the same place where the
existing files were.
5 - Restart SQL Server
Then you might create a database named msdbinstall and restore your msdb
backup from step 1 into msdbinstall. Just in case you need to check out a
few things.
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:9250F5FE-101A-4605-A849-C94B4C1942C7@.microsoft.com...
> Anyway to attach the MSDB database? I have the .mdf and .ldf files from
> before a re-install. It won't allow me to detach MSDB as it says "System
> databases cannot be detached". I would like to replace the current MSDB.
> I
> don't have a database backup file.
> Thanks.
> Ron|||I think this will work only if the file paths match for all the files. SQL
Servere doesn't like it if the files aren't where sysfiles thiks they are.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23X3hsTY9HHA.484@.TK2MSFTNGP06.phx.gbl...
> Ron,
> Provided you really know that this is what you want, you can do the
> following:
> 1 - Backup your existing msdb database.
> 2 - Shut down your SQL Server.
> 3 - Move the existing msdb .mdf and .ldf files to somewhere else.
> 4 - Copy your previous msdb .mdf and .ldf to the same place where the
> existing files were.
> 5 - Restart SQL Server
> Then you might create a database named msdbinstall and restore your msdb
> backup from step 1 into msdbinstall. Just in case you need to check out a
> few things.
> RLF
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:9250F5FE-101A-4605-A849-C94B4C1942C7@.microsoft.com...
>> Anyway to attach the MSDB database? I have the .mdf and .ldf files from
>> before a re-install. It won't allow me to detach MSDB as it says "System
>> databases cannot be detached". I would like to replace the current MSDB.
>> I
>> don't have a database backup file.
>> Thanks.
>> Ron
>|||you can refer the below link,
http://deepakinsql.blogspot.com/2007/08/moving-system-databases-in-sql-2005.html
--
Thanxx
Deepak
"Ron" wrote:
> Anyway to attach the MSDB database? I have the .mdf and .ldf files from
> before a re-install. It won't allow me to detach MSDB as it says "System
> databases cannot be detached". I would like to replace the current MSDB. I
> don't have a database backup file.
> Thanks.
> Ron|||Daniel,
You are right, which is why the replacement msdb files must go back to the
exact same place as occupied by the files at installation time. That is
what my step 4 was trying to say.
RLF
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uelkspZ9HHA.5948@.TK2MSFTNGP04.phx.gbl...
>I think this will work only if the file paths match for all the files. SQL
>Servere doesn't like it if the files aren't where sysfiles thiks they are.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23X3hsTY9HHA.484@.TK2MSFTNGP06.phx.gbl...
>> Ron,
>> Provided you really know that this is what you want, you can do the
>> following:
>> 1 - Backup your existing msdb database.
>> 2 - Shut down your SQL Server.
>> 3 - Move the existing msdb .mdf and .ldf files to somewhere else.
>> 4 - Copy your previous msdb .mdf and .ldf to the same place where the
>> existing files were.
>> 5 - Restart SQL Server
>> Then you might create a database named msdbinstall and restore your msdb
>> backup from step 1 into msdbinstall. Just in case you need to check out
>> a few things.
>> RLF
>>
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:9250F5FE-101A-4605-A849-C94B4C1942C7@.microsoft.com...
>> Anyway to attach the MSDB database? I have the .mdf and .ldf files from
>> before a re-install. It won't allow me to detach MSDB as it says
>> "System
>> databases cannot be detached". I would like to replace the current
>> MSDB. I
>> don't have a database backup file.
>> Thanks.
>> Ron
>>
>|||did this few months ago, works!!!
Russell Fields wrote:
> Daniel,
> You are right, which is why the replacement msdb files must go back to the
> exact same place as occupied by the files at installation time. That is
> what my step 4 was trying to say.
> RLF
>
> "Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
> news:uelkspZ9HHA.5948@.TK2MSFTNGP04.phx.gbl...
>> I think this will work only if the file paths match for all the files. SQL
>> Servere doesn't like it if the files aren't where sysfiles thiks they are.
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23X3hsTY9HHA.484@.TK2MSFTNGP06.phx.gbl...
>> Ron,
>> Provided you really know that this is what you want, you can do the
>> following:
>> 1 - Backup your existing msdb database.
>> 2 - Shut down your SQL Server.
>> 3 - Move the existing msdb .mdf and .ldf files to somewhere else.
>> 4 - Copy your previous msdb .mdf and .ldf to the same place where the
>> existing files were.
>> 5 - Restart SQL Server
>> Then you might create a database named msdbinstall and restore your msdb
>> backup from step 1 into msdbinstall. Just in case you need to check out
>> a few things.
>> RLF
>>
>> "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> news:9250F5FE-101A-4605-A849-C94B4C1942C7@.microsoft.com...
>> Anyway to attach the MSDB database? I have the .mdf and .ldf files from
>> before a re-install. It won't allow me to detach MSDB as it says
>> "System
>> databases cannot be detached". I would like to replace the current
>> MSDB. I
>> don't have a database backup file.
>> Thanks.
>> Ron
>>
>|||Thank you very much - that worked just fine.
"Russell Fields" wrote:
> Ron,
> Provided you really know that this is what you want, you can do the
> following:
> 1 - Backup your existing msdb database.
> 2 - Shut down your SQL Server.
> 3 - Move the existing msdb .mdf and .ldf files to somewhere else.
> 4 - Copy your previous msdb .mdf and .ldf to the same place where the
> existing files were.
> 5 - Restart SQL Server
> Then you might create a database named msdbinstall and restore your msdb
> backup from step 1 into msdbinstall. Just in case you need to check out a
> few things.
> RLF
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:9250F5FE-101A-4605-A849-C94B4C1942C7@.microsoft.com...
> > Anyway to attach the MSDB database? I have the .mdf and .ldf files from
> > before a re-install. It won't allow me to detach MSDB as it says "System
> > databases cannot be detached". I would like to replace the current MSDB.
> > I
> > don't have a database backup file.
> >
> > Thanks.
> >
> > Ron
>
>

Attaching MDF and LDF Files Error

i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database on
customer site installed on C Partition ...MDF file is 300 MB but LDF file
became 12 GB which took all of the C Partition Space.. Our Tech Supp Person
have moved these Physical Files From C to D partition and Deleted the files
and database from C partition and he created new database and tried to Attach
from D partion but that gives an error(Error 9003: The
LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is invalid)
another problem is that latest backup is not available only old backup
available.
we restored old backup and tried to attach those files (300 MB and 12 GB)
but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
DIFFERENT DATABASES.
please can any one help in this case ...my email is raza_noor@.hotmail.comHi
Was the file detached correctly?
Try renaming the log file and using sp_attach_single_file_db to create a new
log, and then detaching/move/attaching the log or in SQL 2005 you can use
CREATE DATABASE with the ATTACH_REBUILD_LOG option instead of
sp_attach_single_file_db.
John
"razanoor" <razanoor@.discussions.microsoft.com> wrote in message
news:4D14A1C6-EE37-463B-A4E3-9B4F79BBA684@.microsoft.com...
> i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database
> on
> customer site installed on C Partition ...MDF file is 300 MB but LDF file
> became 12 GB which took all of the C Partition Space.. Our Tech Supp
> Person
> have moved these Physical Files From C to D partition and Deleted the
> files
> and database from C partition and he created new database and tried to
> Attach
> from D partion but that gives an error(Error 9003: The
> LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is
> invalid)
> another problem is that latest backup is not available only old backup
> available.
> we restored old backup and tried to attach those files (300 MB and 12 GB)
> but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
> DIFFERENT DATABASES.
> please can any one help in this case ...my email is raza_noor@.hotmail.com|||Hi
Does he try top attach with single file option (for more details please see
BOL) i.e without specifying .LDF file?
Don't you have proper BACKUP?
"razanoor" <razanoor@.discussions.microsoft.com> wrote in message
news:4D14A1C6-EE37-463B-A4E3-9B4F79BBA684@.microsoft.com...
> i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database
> on
> customer site installed on C Partition ...MDF file is 300 MB but LDF file
> became 12 GB which took all of the C Partition Space.. Our Tech Supp
> Person
> have moved these Physical Files From C to D partition and Deleted the
> files
> and database from C partition and he created new database and tried to
> Attach
> from D partion but that gives an error(Error 9003: The
> LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is
> invalid)
> another problem is that latest backup is not available only old backup
> available.
> we restored old backup and tried to attach those files (300 MB and 12 GB)
> but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
> DIFFERENT DATABASES.
> please can any one help in this case ...my email is raza_noor@.hotmail.com|||If the Tech Supp Person did no detach database file before move it, he is
not qualified to manage a server/computer with production SQL Server.
"razanoor" <razanoor@.discussions.microsoft.com> wrote in message
news:4D14A1C6-EE37-463B-A4E3-9B4F79BBA684@.microsoft.com...
> i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database
> on
> customer site installed on C Partition ...MDF file is 300 MB but LDF file
> became 12 GB which took all of the C Partition Space.. Our Tech Supp
> Person
> have moved these Physical Files From C to D partition and Deleted the
> files
> and database from C partition and he created new database and tried to
> Attach
> from D partion but that gives an error(Error 9003: The
> LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is
> invalid)
> another problem is that latest backup is not available only old backup
> available.
> we restored old backup and tried to attach those files (300 MB and 12 GB)
> but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
> DIFFERENT DATABASES.
> please can any one help in this case ...my email is raza_noor@.hotmail.com

Attaching MDF and LDF Files Error

i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database on
customer site installed on C Partition ...MDF file is 300 MB but LDF file
became 12 GB which took all of the C Partition Space.. Our Tech Supp Person
have moved these Physical Files From C to D partition and Deleted the files
and database from C partition and he created new database and tried to Attach
from D partion but that gives an error(Error 9003: The
LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is invalid)
another problem is that latest backup is not available only old backup
available.
we restored old backup and tried to attach those files (300 MB and 12 GB)
but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
DIFFERENT DATABASES.
please can any one help in this case ...my email is raza_noor@.hotmail.com
Hi
Was the file detached correctly?
Try renaming the log file and using sp_attach_single_file_db to create a new
log, and then detaching/move/attaching the log or in SQL 2005 you can use
CREATE DATABASE with the ATTACH_REBUILD_LOG option instead of
sp_attach_single_file_db.
John
"razanoor" <razanoor@.discussions.microsoft.com> wrote in message
news:4D14A1C6-EE37-463B-A4E3-9B4F79BBA684@.microsoft.com...
> i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database
> on
> customer site installed on C Partition ...MDF file is 300 MB but LDF file
> became 12 GB which took all of the C Partition Space.. Our Tech Supp
> Person
> have moved these Physical Files From C to D partition and Deleted the
> files
> and database from C partition and he created new database and tried to
> Attach
> from D partion but that gives an error(Error 9003: The
> LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is
> invalid)
> another problem is that latest backup is not available only old backup
> available.
> we restored old backup and tried to attach those files (300 MB and 12 GB)
> but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
> DIFFERENT DATABASES.
> please can any one help in this case ...my email is raza_noor@.hotmail.com
|||Hi
Does he try top attach with single file option (for more details please see
BOL) i.e without specifying .LDF file?
Don't you have proper BACKUP?
"razanoor" <razanoor@.discussions.microsoft.com> wrote in message
news:4D14A1C6-EE37-463B-A4E3-9B4F79BBA684@.microsoft.com...
> i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database
> on
> customer site installed on C Partition ...MDF file is 300 MB but LDF file
> became 12 GB which took all of the C Partition Space.. Our Tech Supp
> Person
> have moved these Physical Files From C to D partition and Deleted the
> files
> and database from C partition and he created new database and tried to
> Attach
> from D partion but that gives an error(Error 9003: The
> LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is
> invalid)
> another problem is that latest backup is not available only old backup
> available.
> we restored old backup and tried to attach those files (300 MB and 12 GB)
> but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
> DIFFERENT DATABASES.
> please can any one help in this case ...my email is raza_noor@.hotmail.com
|||If the Tech Supp Person did no detach database file before move it, he is
not qualified to manage a server/computer with production SQL Server.
"razanoor" <razanoor@.discussions.microsoft.com> wrote in message
news:4D14A1C6-EE37-463B-A4E3-9B4F79BBA684@.microsoft.com...
> i am facing a problem for SQL 2000 Database .. We have SQL 2000 Database
> on
> customer site installed on C Partition ...MDF file is 300 MB but LDF file
> became 12 GB which took all of the C Partition Space.. Our Tech Supp
> Person
> have moved these Physical Files From C to D partition and Deleted the
> files
> and database from C partition and he created new database and tried to
> Attach
> from D partion but that gives an error(Error 9003: The
> LSN(12560:120211:1) Passed to log scan in database 'DatabaseName' is
> invalid)
> another problem is that latest backup is not available only old backup
> available.
> we restored old backup and tried to attach those files (300 MB and 12 GB)
> but didnt work.. another error comes that CAN NOT ATTACH FILES FROM
> DIFFERENT DATABASES.
> please can any one help in this case ...my email is raza_noor@.hotmail.com

attaching mdf & ldf files

Hi,
Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
database, formatted that server with the help of a sys admin.
I am aware that attach wont work 90% times without a detach. Now the dev.
guys say that they have some sp codes in that db and expecting me to make DB
available.
I am sure that chances are less but I am giving a try and this is the
present situation.
step1:
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'd:\backup\MyDB_Data.mdf',
@.filename2 = N'd:\backup\MyDB_log.ldf'
output:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
step2:
EXEC sp_attach_single_file_db @.dbname = 'MyDB',
@.physname = 'd:\bkup\MyDB_Data.mdf'
output:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000028840000 in
file 'd:\backup\MyDB_Data.mdf'.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
New log file 'd:\backup\MyDB_log.LDF' was created.
Connection Broken
Whats happening & what else i can try.
Thanks
reddi
Reddi,
Have you recreated the exact same paths that were on the original build?
Have you got a backup you can restore from?
My hopes are not high that you will be able to recover this database. If
you are really desperate you should give MS PSS a call.
http://www.mssqlserver.com/faq/general-pss.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Reddi wrote:
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>
|||Hi Mark,
Have you recreated the exact same paths that were on the original build?
--These are just mdf and ldf files which were moved onto other server by
stopping the SQL Server. Do you mean to say that I need to create a similar
directory path as in source server and try to attach?
Have you got a backup you can restore from?
--yes but a old one..
Thanks
Reedi
"Mark Allison" wrote:

> Reddi,
> Have you recreated the exact same paths that were on the original build?
> Have you got a backup you can restore from?
> My hopes are not high that you will be able to recover this database. If
> you are really desperate you should give MS PSS a call.
> http://www.mssqlserver.com/faq/general-pss.asp
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Reddi wrote:
>
|||On the second step, did you check if your database was restored?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>
|||Hi Peter,
Thanks for the input.. i havent check that.. request u to keep this thread
on watch
will be back soon
reedi
"Peter Yeoh" wrote:

> On the second step, did you check if your database was restored?
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> a
> DB
> Files\Microsoft
>
>
|||Hi Peter,
Extremely sorry for the delay.. after executing step 2, I dont see that
database created in EM.
Thanks
Reedi.
"Reddi" wrote:
[vbcol=seagreen]
> Hi Peter,
> Thanks for the input.. i havent check that.. request u to keep this thread
> on watch
> will be back soon
> reedi
>
> "Peter Yeoh" wrote:
|||Hi Reddi,
This might of help to you
http://www.spaceprogram.com/knowledg...leted_log.html
http://www.spaceprogram.com/knowledg..._received.html
http://forums1.itrc.hp.com/service/f...hreadId=653793
Regards
Ravi
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message news:<A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com>...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi

attaching mdf & ldf files

Hi,
Our dev. guys have stopped sequel & took the backup of mdf and ldf file of a
database, formatted that server with the help of a sys admin.
I am aware that attach wont work 90% times without a detach. Now the dev.
guys say that they have some sp codes in that db and expecting me to make D
B
available.
I am sure that chances are less but I am giving a try and this is the
present situation.
step1:
EXEC sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'd:\backup\MyDB_Data.mdf',
@.filename2 = N'd:\backup\MyDB_log.ldf'
output:
Server: Msg 5173, Level 16, State 2, Line 1
Cannot associate files with different databases.
step2:
EXEC sp_attach_single_file_db @.dbname = 'MyDB',
@.physname = 'd:\bkup\MyDB_Data.mdf'
output:
Server: Msg 823, Level 24, State 2, Line 1
I/O error (bad page ID) detected during read at offset 0x00000028840000 in
file 'd:\backup\MyDB_Data.mdf'.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
New log file 'd:\backup\MyDB_log.LDF' was created.
Connection Broken
Whats happening & what else i can try.
Thanks
reddiReddi,
Have you recreated the exact same paths that were on the original build?
Have you got a backup you can restore from?
My hopes are not high that you will be able to recover this database. If
you are really desperate you should give MS PSS a call.
http://www.mssqlserver.com/faq/general-pss.asp
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Reddi wrote:
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>|||Hi Mark,
Have you recreated the exact same paths that were on the original build?
--These are just mdf and ldf files which were moved onto other server by
stopping the SQL Server. Do you mean to say that I need to create a similar
directory path as in source server and try to attach?
Have you got a backup you can restore from?
--yes but a old one..
Thanks
Reedi
"Mark Allison" wrote:

> Reddi,
> Have you recreated the exact same paths that were on the original build?
> Have you got a backup you can restore from?
> My hopes are not high that you will be able to recover this database. If
> you are really desperate you should give MS PSS a call.
> http://www.mssqlserver.com/faq/general-pss.asp
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Reddi wrote:
>|||On the second step, did you check if your database was restored?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message
news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi
>|||Hi Peter,
Thanks for the input.. i havent check that.. request u to keep this thread
on watch
will be back soon
reedi
"Peter Yeoh" wrote:

> On the second step, did you check if your database was restored?
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Reddi" <Reddi@.discussions.microsoft.com> wrote in message
> news:A0C79772-6A2F-471D-B842-ECD98AC9F244@.microsoft.com...
> a
> DB
> Files\Microsoft
>
>|||Hi Peter,
Extremely sorry for the delay.. after executing step 2, I dont see that
database created in EM.
Thanks
Reedi.
"Reddi" wrote:
[vbcol=seagreen]
> Hi Peter,
> Thanks for the input.. i havent check that.. request u to keep this thread
> on watch
> will be back soon
> reedi
>
> "Peter Yeoh" wrote:
>|||Hi Reddi,
This might of help to you
http://www.spaceprogram.com/knowled...s_received.html
http://forums1.itrc.hp.com/service/...threadId=653793
Regards
Ravi
"Reddi" <Reddi@.discussions.microsoft.com> wrote in message news:<A0C79772-6A2F-471D-B842-ECD
98AC9F244@.microsoft.com>...
> Hi,
> Our dev. guys have stopped sequel & took the backup of mdf and ldf file of
a
> database, formatted that server with the help of a sys admin.
> I am aware that attach wont work 90% times without a detach. Now the dev.
> guys say that they have some sp codes in that db and expecting me to make
DB
> available.
> I am sure that chances are less but I am giving a try and this is the
> present situation.
> step1:
> EXEC sp_attach_db @.dbname = N'MyDB',
> @.filename1 = N'd:\backup\MyDB_Data.mdf',
> @.filename2 = N'd:\backup\MyDB_log.ldf'
>
> output:
> Server: Msg 5173, Level 16, State 2, Line 1
> Cannot associate files with different databases.
>
> step2:
> EXEC sp_attach_single_file_db @.dbname = 'MyDB',
> @.physname = 'd:\bkup\MyDB_Data.mdf'
> output:
> Server: Msg 823, Level 24, State 2, Line 1
> I/O error (bad page ID) detected during read at offset 0x00000028840000 in
> file 'd:\backup\MyDB_Data.mdf'.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\MyDB_Log.LDF' may be incorrect.
> New log file 'd:\backup\MyDB_log.LDF' was created.
> Connection Broken
> Whats happening & what else i can try.
> Thanks
> reddi

Tuesday, March 27, 2012

Auto delete of backup files after 1 week problem

Thursday, March 22, 2012

Authentication permissions

Thursday, March 8, 2012

Audit Trace Files

Wednesday, March 7, 2012

Audit Log

Saturday, February 25, 2012

Attribute- vs. Element-Centric XML

Friday, February 24, 2012

Attempted to move System table

Attempted to move System table

Sunday, February 19, 2012

Attempted to move System table

Thursday, February 16, 2012

Attaching the log file and the mdf files of the database.

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching MSDB database

Attaching MDF and LDF Files Error

Attaching MDF and LDF Files Error

attaching mdf & ldf files

attaching mdf & ldf files

Attach remote DB file

Blog Archive

About Me