HELP on How to move database files

If you are able to detach/ attach succesfully, whats the
problem?

>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...[vbcol=seagreen]
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> database files around. I
> physical location of the
> a different
> log file in its new
> log file but does
> file that I have
> moving/removing database|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!

>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
to[vbcol=seagreen]
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.

>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
make a copy of both[vbcol=seagreen]
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Machttp://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- MacIf you are able to detach/ attach succesfully, whats the
problem?
>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>|||I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> >--Original Message--
> >For performance reason, I have decided to move my
> database files around. I
> >have tried using Attach and Detach database to move
> physical location of the
> >files successfully.
> >
> >What I want to do is to move the transaction log file to
> a different
> >location or even better get rid of it and create a new
> log file in its new
> >location. I have tried dbcc shrinkfile to truncate the
> log file but does
> >not allow me to remove it or empty it into another log
> file that I have
> >created in a new location. Any suggestion on
> moving/removing database
> >files are appreciated.
> >
> >- Mac
> >
> >
> >.
> >|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file
to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> > If Im not mistaken, you would:
> >
> > Detach.
> > Turn off SQL.
> > Delete log file.
> > Turn on SQL.
> > Attach single file with sp_attach_single_file_db.
> >
> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.
>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
>> ChrisR wrote:
>> > If Im not mistaken, you would:
>> >
>> > Detach.
>> > Turn off SQL.
>> > Delete log file.
>> > Turn on SQL.
>> > Attach single file with sp_attach_single_file_db.
>> >
>> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>> I've never had any problems doing this. I'd certainly
make a copy of both
>> the MDF and LDF files before doing it, though.
>> John.
>>
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
> >I want to drop the log file altogether and start with a new one in a new
> > location. I could not do this with Detach/Attach as far as I can tell.
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> >> If you are able to detach/ attach succesfully, whats the
> >> problem?
> >>
> >>
> >> >--Original Message--
> >> >For performance reason, I have decided to move my
> >> database files around. I
> >> >have tried using Attach and Detach database to move
> >> physical location of the
> >> >files successfully.
> >> >
> >> >What I want to do is to move the transaction log file to
> >> a different
> >> >location or even better get rid of it and create a new
> >> log file in its new
> >> >location. I have tried dbcc shrinkfile to truncate the
> >> log file but does
> >> >not allow me to remove it or empty it into another log
> >> file that I have
> >> >created in a new location. Any suggestion on
> >> moving/removing database
> >> >files are appreciated.
> >> >
> >> >- Mac
> >> >
> >> >
> >> >.
> >> >
> >
> >
>

HELP on How to move database files

If you are able to detach/ attach succesfully, whats the
problem?

>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>
I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...[vbcol=seagreen]
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> database files around. I
> physical location of the
> a different
> log file in its new
> log file but does
> file that I have
> moving/removing database
|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!

>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
to
>
>.
>
|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.
|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>
|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.

>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message[vbcol=seagreen]
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
make a copy of both
>
>.
>
|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>
|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>

HELP on How to move database files

For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Mac
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>
sql

Help needed to attach a DB

I detached a DB, now when I am trying to reattch it I cannot find the
data or the log file. I know I have not deleted anything from the
drives the only thing I am not sure exactly where they were located
prior to the dettach. This is on a test box but we still need to
restore the db. Is there anyway from system tables I could find where
the data file is. I looked for *.mdf files but cannot find the correct
.mdf file. Is it possiblefor the data file to have a differnt
extension? Is it possible for the data file to be located in any
network drive, I did not think this was possible in 2000 but I am using
2005 so I thought I should throw that out there ? Finally will it be
possible to restore the master db then look at the sysdatabse table and
see where it was located prior to the detach, i want to do this only if
it is possible to do so without effecting any other db's on this box.
Any ideas how to troubleshoot this, I am just baffled.
ThanksHi,
By default SQL 2005 uses MDF and LDF extension only. Once you detach the
database all the reference
for that database willl be moved from SQL Server. For me the only way to
identify the location is by restoring the Master database into a new SQL
Server 2005 instance. (Master can be restored only above a Master database in
single user mode). After resting execute the below query to get the
file location of the database you deleted.
select * from sys.master_files
Thanks
Hari
SQL Server MVP
"shub" wrote:
> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>|||Hi
If you have a backup of the database you could use RESTORE FILELISTONLY
command to get the file locations.
John
"shub" wrote:
> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>

Help needed to attach a DB

I detached a DB, now when I am trying to reattch it I cannot find the
data or the log file. I know I have not deleted anything from the
drives the only thing I am not sure exactly where they were located
prior to the dettach. This is on a test box but we still need to
restore the db. Is there anyway from system tables I could find where
the data file is. I looked for *.mdf files but cannot find the correct
.mdf file. Is it possiblefor the data file to have a differnt
extension? Is it possible for the data file to be located in any
network drive, I did not think this was possible in 2000 but I am using
2005 so I thought I should throw that out there ? Finally will it be
possible to restore the master db then look at the sysdatabse table and
see where it was located prior to the detach, i want to do this only if
it is possible to do so without effecting any other db's on this box.
Any ideas how to troubleshoot this, I am just baffled.
ThanksHi,
By default SQL 2005 uses MDF and LDF extension only. Once you detach the
database all the reference
for that database willl be moved from SQL Server. For me the only way to
identify the location is by restoring the Master database into a new SQL
Server 2005 instance. (Master can be restored only above a Master database i
n
single user mode). After resting execute the below query to get the
file location of the database you deleted.
select * from sys.master_files
Thanks
Hari
SQL Server MVP
"shub" wrote:

> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>|||Hi
If you have a backup of the database you could use RESTORE FILELISTONLY
command to get the file locations.
John
"shub" wrote:

> I detached a DB, now when I am trying to reattch it I cannot find the
> data or the log file. I know I have not deleted anything from the
> drives the only thing I am not sure exactly where they were located
> prior to the dettach. This is on a test box but we still need to
> restore the db. Is there anyway from system tables I could find where
> the data file is. I looked for *.mdf files but cannot find the correct
> ..mdf file. Is it possiblefor the data file to have a differnt
> extension? Is it possible for the data file to be located in any
> network drive, I did not think this was possible in 2000 but I am using
> 2005 so I thought I should throw that out there ? Finally will it be
> possible to restore the master db then look at the sysdatabse table and
> see where it was located prior to the detach, i want to do this only if
> it is possible to do so without effecting any other db's on this box.
> Any ideas how to troubleshoot this, I am just baffled.
> Thanks
>

help needed on a silly query :P

hi ...
i m a new member
i want a help on this query(new user of sql :D )
yaa and the query is

create a rule and attach it to the service_code(column) of airline_service(Table) alow only the values 'cc','n' and 'wc' to be entered into the column

plz tell mee... :confused: ..i wlll be gr8 fulllllllllllllllllllllllllllllllll ;)I suggest you to create a CHECK CONSTRAINT instead of a rule:

ALTER TABLE dbo.airline_service
ADD CONSTRAINT chk_servicecode CHECK (service_code IN ('cc', 'n', 'wc'))

Davide Mauri
http://www.davidemauri.it|||You could create a trigger or a check. I think a check is more likely what you'd like, fe:

use monkey
go

create table table1 (mycolumn varchar(10))
go

alter table table1 with check
add constraint df__myrule check
( mycolumn not in ('yes', 'allowed'))
go

insert into table1 (mycolumn) values ('yes')
insert into table1 (mycolumn) values ('no')
go

select *
from table1

drop table table1
go

See BOL on CHECK

EDIT: sniped!|||thank youuuuuuuuuuuuuuuuuuuuuuu :o|||I hope your professor gives you full credit for that, but next time try to do your own homework first.|||I doubt if the Professor will give a credit!

He asked for a rule so it should be:

CREATE RULE list_rule
AS
@.list IN ('cc', 'n', 'wc')

nomis|||That's for sure, but rules are to be considered deprecated, only for backward compatibility. So the CHECK solution is really better. :-)

Anyway, after adding the rule it must be binded with sp_bindrule

Help me resolve this error."Missing parameter field current value." Code Attach

Hi ,
am getting this error."Missing parameter field current value." When i Use this line of code
crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues() it says "ApplyCurrentValues is not a member of CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition".How do i get rid of this error.
Also is there anyway that without Exporting , i Can Print the Report from vb.net application directly using default Printer.
Code is:
Private Sub Btn_Export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Export.Click
Try
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = "WASA00150"
.DatabaseName = "iCalls"
.UserID = "sa"
.Password = "courage"
End With
Me.CrystalReportViewer1.ParameterFieldInfo.Clear()
If Me.txtSTdate.Text.Trim.Length > 0 Then
Me.CrystalReportViewer1.ReportSource = Server.MapPath("iCalls_CrystalReport_Department.rpt")
Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
Dim Per As New ParameterField
Per.ParameterFieldName = "Period"
Dim Period_Value As New ParameterRangeValue
Period_Value.StartValue = Me.txtSTdate.Text
Period_Value.EndValue = Me.txtCLdate.Text
Per.CurrentValues.Add(Period_Value)
ParamFields.Add(Per)
End If
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
Me.CrystalReportViewer1.RefreshReport()
CrystalReportViewer1.Visible = True
Dim exportPath As String = "D:\SampleCrystalReports\iCalls_Export\test1.pdf"
Dim crExportOptions As ExportOptions
Dim crDestOptions As New DiskFileDestinationOptions
crDestOptions.DiskFileName = exportPath
crExportOptions = crReportDocument.ExportOptions
crExportOptions.DestinationOptions = crDestOptions
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()
crReportDocument.Export()
Catch ex As Exception
lblmsg.Text = ex.Message.ToString
End Try
End Sub
Many Thanks.Not sure if this is it, but looking at the code this line.

'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()

has a ' at the begining, is that supposed to be there ?