detaching db and attaching to another server

Using SS2000 SP4. I've detached a db from server A and been able to attach i
t
without any problem to server B. However, if I try to attach it to server C,
the database shows as read-only. The same thing happens with other databases
that I try to attach to server C.
Any idea why?
Thanks,
--
Dan D.Dan D. wrote:
> Using SS2000 SP4. I've detached a db from server A and been able to attach
it
> without any problem to server B. However, if I try to attach it to server
C,
> the database shows as read-only. The same thing happens with other databas
es
> that I try to attach to server C.
> Any idea why?
> Thanks,
> --
> Dan D.
Check to see that you are not putting your database data files in a
read only folder. I had read only to true by accident and got the same
symptoms you did. When you turn off read only, you will have to
re-attach.
Tony|||That was it. Thanks. Do you know what the minimum permissions are? I gave
full control to the user that the services are using but if the user doesn't
need full control, I'd rather reduce them.
--
Dan D.
"Tony" wrote:

> Dan D. wrote:
> Check to see that you are not putting your database data files in a
> read only folder. I had read only to true by accident and got the same
> symptoms you did. When you turn off read only, you will have to
> re-attach.
> Tony
>

detaching db and attaching to another server

Using SS2000 SP4. I've detached a db from server A and been able to attach it
without any problem to server B. However, if I try to attach it to server C,
the database shows as read-only. The same thing happens with other databases
that I try to attach to server C.
Any idea why?
Thanks,
--
Dan D.Dan D. wrote:
> Using SS2000 SP4. I've detached a db from server A and been able to attach it
> without any problem to server B. However, if I try to attach it to server C,
> the database shows as read-only. The same thing happens with other databases
> that I try to attach to server C.
> Any idea why?
> Thanks,
> --
> Dan D.
Check to see that you are not putting your database data files in a
read only folder. I had read only to true by accident and got the same
symptoms you did. When you turn off read only, you will have to
re-attach.
Tony|||That was it. Thanks. Do you know what the minimum permissions are? I gave
full control to the user that the services are using but if the user doesn't
need full control, I'd rather reduce them.
--
Dan D.
"Tony" wrote:
> Dan D. wrote:
> > Using SS2000 SP4. I've detached a db from server A and been able to attach it
> > without any problem to server B. However, if I try to attach it to server C,
> > the database shows as read-only. The same thing happens with other databases
> > that I try to attach to server C.
> >
> > Any idea why?
> >
> > Thanks,
> > --
> > Dan D.
> Check to see that you are not putting your database data files in a
> read only folder. I had read only to true by accident and got the same
> symptoms you did. When you turn off read only, you will have to
> re-attach.
> Tony
>

Detaching and Re-Attaching Loses Stored Procedures

Hi, I've just detached using QA and sp_detach_db a database that I have been
working on for a client and taken it to the clients site and using QA and
sp_attach_db attached it to their SQL 2000 Server.
The problem is that a) there are none of my stored procedures for that db on
their server, b) when re-attaching it back to my SQL Server there are none
of my stored procedures and worst still c) who forgot to backit up first!!
I thought all user stored procedures were stored within the mdf files so why
have they not gone with the db, tables, etc. The user sps did NOT start with
sp_ by the way.
Any help would be much appreciated.
Thanks
David Smith
MCP MCSE MCDBA <-- (ha, ha - I'm supposed to know!)Look at that issues:
http://groups.google.de/groups?q=at...c.rr.com&rnum=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David Smith" <david.smith@.lonsdale.co.uk> schrieb im Newsbeitrag
news:uGfYA8bSFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi, I've just detached using QA and sp_detach_db a database that I have
> been working on for a client and taken it to the clients site and using QA
> and sp_attach_db attached it to their SQL 2000 Server.
> The problem is that a) there are none of my stored procedures for that db
> on their server, b) when re-attaching it back to my SQL Server there are
> none of my stored procedures and worst still c) who forgot to backit up
> first!!
> I thought all user stored procedures were stored within the mdf files so
> why have they not gone with the db, tables, etc. The user sps did NOT
> start with sp_ by the way.
> Any help would be much appreciated.
> Thanks
> David Smith
> MCP MCSE MCDBA <-- (ha, ha - I'm supposed to know!)
>

Detaching & Attaching Database

Hello Group.
I have the following problem using SQL Server 2000 on WinXP machine.
I detached a SQL database and then performed a windows copy of the database
to another folder on the system.
I then renamed the database and tried to attach the renamed database to SQL.
SQL sees the old name and says there is already a database with that name
attached.
How can I solve this problem?
How can I get SQL to see the new name given to the MDF and LDF files?
Thanks,
Terry"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>
Renaming the MDF / LDF files has nothing to do with renaming the database.
The name for the database when it is attached is specified by the first
parameter of sp_attach_db. Example:
EXEC sp_attach_db 'new_db_name',
'd:\mssql\data\db_file_name.mdf',
'd:\mssql\data\db_file_name.ldf'
David Portas
SQL Server MVP
--|||Hi Terry
The name of the database does not have to have anything to do with the names
of the database files. So when you say you renamed 'the database' do you
mean you renamed the files you moved, or renamed the database on the server?
The original name of the database is stored with the files, however, even if
you change the name of the files. If you want to attach some database files
and give the database contained on those files another name than the one it
originally had, you have to specify that when you do the attach.
I suggest you use the command sp_attach_db, following the examples in the
Books Online. This will allow you to specify the name of the database you
are attaching, and the physical names of all the files you want to attach.
If you still are unsuccessful, you can then post the exact command you are
using and the exact error message you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Terry" <tgwillett@.cox.net> wrote in message
news:Trhwf.51529$ih5.12670@.dukeread11...
> Hello Group.
> I have the following problem using SQL Server 2000 on WinXP machine.
> I detached a SQL database and then performed a windows copy of the
> database to another folder on the system.
> I then renamed the database and tried to attach the renamed database to
> SQL.
> SQL sees the old name and says there is already a database with that name
> attached.
> How can I solve this problem?
> How can I get SQL to see the new name given to the MDF and LDF files?
> Thanks,
> Terry
>|||Okay, but how do I accomplish this with enterprise manager?
In the dialog box that appears when you go to attach the database, it gives
you the orginal database name.
In the box "attach as" I give it the new name and get the error that it
already exits.
What am I missing here?
Thanks, for your quick response.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
> Renaming the MDF / LDF files has nothing to do with renaming the database.
> The name for the database when it is attached is specified by the first
> parameter of sp_attach_db. Example:
> EXEC sp_attach_db 'new_db_name',
> 'd:\mssql\data\db_file_name.mdf',
> 'd:\mssql\data\db_file_name.ldf'
> --
> David Portas
> SQL Server MVP
> --
>|||Okay, your statement
"The original name of the database is stored with the files" explains what I
was missing.
Now I understand. I did not know this.
Thanks,
Kalen
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Edin%23KFGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hi Terry
> The name of the database does not have to have anything to do with the
> names of the database files. So when you say you renamed 'the database' do
> you mean you renamed the files you moved, or renamed the database on the
> server?
> The original name of the database is stored with the files, however, even
> if you change the name of the files. If you want to attach some database
> files and give the database contained on those files another name than the
> one it originally had, you have to specify that when you do the attach.
> I suggest you use the command sp_attach_db, following the examples in the
> Books Online. This will allow you to specify the name of the database you
> are attaching, and the physical names of all the files you want to attach.
> If you still are unsuccessful, you can then post the exact command you are
> using and the exact error message you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
>
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> "Terry" <tgwillett@.cox.net> wrote in message
> news:Trhwf.51529$ih5.12670@.dukeread11...
I don't know about accomplishing this in Enterprise Manager because I rarely
use EM that way. In my opinion it would be much easier to type the
sp_attach_db command in Query Analyzer.
David Portas
SQL Server MVP
--|||Okay,
Thanks for your help.
Much appreciated.
Terry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eEqu3XLFGHA.1288@.TK2MSFTNGP09.phx.gbl...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:uClxr8KFGHA.2064@.TK2MSFTNGP09.phx.gbl...
> I don't know about accomplishing this in Enterprise Manager because I
> rarely use EM that way. In my opinion it would be much easier to type the
> sp_attach_db command in Query Analyzer.
> --
> David Portas
> SQL Server MVP
> --
>

Detached model and msdb - sql server stopped!

Hi,
I detached my model and msdb databases to move the data
and log files. The server went down prior to my
reattaching the model. Now, every time I start SQL, it
goes down when I try to connect, or change options, or try
to reattach model, etc.
Can you help?
Lisa.Perhaps you can start SQL Server from the command prompt with mimimal =configuration and then fix the problems. ---
This information is from Books Online. ---
Starting SQL Server with Minimal Configuration
If you have configuration problems that prevent the server from =starting, you can start an instance of Microsoft=AE SQL ServerT using =the minimal configuration startup option. This is the startup option -f. =Starting an instance of SQL Server with minimal configuration places the =server in single-user mode automatically.
When you start an instance of SQL Server in minimal configuration mode:
Only a single user can connect, and the CHECKPOINT process is not =executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
The sp_configure stored procedure allow updates option is enabled. By =default, the allow updates option is disabled. After the server has been started with minimal configuration, you should =change the appropriate server option value or values, stop, and then =restart the server.
Important Stop the SQL Server Agent service before connecting to an =instance of SQL Server in minimal configuration mode. Otherwise, the SQL =Server Agent service uses the connection, thereby blocking it.
To start SQL Server with minimal configuration
Command Prompt
How to start the default instance of SQL Server with minimal =configuration (Command Prompt)
To start the default instance of SQL Server with minimal configuration
From a command prompt, enter the following command to start the default =instance of Microsoft=AE SQL ServerT as a service: sqlservr -c -f
Note You must switch to the appropriate directory (for the instance of =SQL Server you want to start) in the command window before starting =sqlservr.exe.
-- Keith, SQL Server MVP
"Lisa" <lisajphillip@.kerzner.com> wrote in message =news:02f701c34b03$838bace0$a301280a@.phx.gbl...
> Hi,
> > I detached my model and msdb databases to move the data > and log files. The server went down prior to my > reattaching the model. Now, every time I start SQL, it > goes down when I try to connect, or change options, or try > to reattach model, etc.
> Can you help?
> > Lisa.
>|||Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>|||Great! -- Keith, SQL Server MVP
"Lisa" <lisaj.phillip@.kerzner.com> wrote in message =news:04ed01c34b0f$86f79130$a101280a@.phx.gbl...
Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>

detached db cant recover

A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log?
Any ideas?
PLEASE HELP!
Richard
Hi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on?
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||Take a look into the below KB article.
http://support.microsoft.com/default...b;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on?
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>
|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error?
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log?
> Any ideas?
> PLEASE HELP!
> Richard
>
|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error?
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>

detached db cant recover

A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset
of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in data
base
[envision] (7). The OS file handle is 0x0000055C. The offset of the la
test
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/defaul...kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF]
in
> database [envision] (7). The OS file handle is 0x0000055C. The offse
t of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery i
t
> crashes with a primary key violation at abuot 9% complete. I am starting
to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>

detached db cant recover

A genius here at work decided to detach a database because the disk the log
file was on was full. He copied the log to a different drive and now we
cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
looks like it does the analysis to reattach in about 5 minutes then starts
what appears to be a 60 hour recovery cycle.
Is there anything I can do to speed this up? How can I truncate a detached
log'
Any ideas'
PLEASE HELP!
RichardHi Richard
Was the detach successful, as far as you know, i.e. no error messages
reported?
If so, you can try attaching without the log and have SQL Server build a new
one. Just change the name of the old log file so SQL Server can't find it,
and point to the primary file for the attach.
--
HTH
Kalen Delaney, SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Ok That seems to be working but it is PAINFULLY slow.
Now I am getting these messages in the log. What the heck is going on'
SQL Server has encountered 13087 occurrence(s) of IO requests taking longer
than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
database [envision] (7). The OS file handle is 0x0000055C. The offset of
the latest long IO is: 0x00000c83a76000
SQL Server has encountered 1 occurrence(s) of IO requests taking longer than
15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in database
[envision] (7). The OS file handle is 0x0000055C. The offset of the latest
long IO is: 0x00000c83a76000
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||Take a look into the below KB article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;897284
Thanks
Hari
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:%23lv8SaQ3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> Ok That seems to be working but it is PAINFULLY slow.
> Now I am getting these messages in the log. What the heck is going on'
> SQL Server has encountered 13087 occurrence(s) of IO requests taking
> longer
> than 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database [envision] (7). The OS file handle is 0x0000055C. The offset of
> the latest long IO is: 0x00000c83a76000
>
> SQL Server has encountered 1 occurrence(s) of IO requests taking longer
> than
> 15 seconds to complete on file [G:\MSSQL\Data\envision_Data.MDF] in
> database
> [envision] (7). The OS file handle is 0x0000055C. The offset of the
> latest
> long IO is: 0x00000c83a76000
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>>A genius here at work decided to detach a database because the disk the
>>log file was on was full. He copied the log to a different drive and now
>>we cant reattach the db. The db is 260 gig, the log us 70 gig. SQL
>>Server looks like it does the analysis to reattach in about 5 minutes then
>>starts what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>|||It just keeps going dowbn hill. Now the problem is that during recovery it
crashes with a primary key violation at abuot 9% complete. I am starting to
panic a little. Any ideas on this new error'
Thanks as ALWAYS!
Richard
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>A genius here at work decided to detach a database because the disk the log
>file was on was full. He copied the log to a different drive and now we
>cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>looks like it does the analysis to reattach in about 5 minutes then starts
>what appears to be a 60 hour recovery cycle.
> Is there anything I can do to speed this up? How can I truncate a
> detached log'
> Any ideas'
> PLEASE HELP!
> Richard
>|||richard
I had same kind of problem with small db when i was copying & attaching
files on different drive. transaction rollback happened.
( i dont know that time,the tlog was big)
Then I attached the original files as different dbname and took a
backup. Run checkpoint & backup before detaching db
Richard Douglass wrote:
> It just keeps going dowbn hill. Now the problem is that during recovery it
> crashes with a primary key violation at abuot 9% complete. I am starting to
> panic a little. Any ideas on this new error'
> Thanks as ALWAYS!
> Richard
>
> "Richard Douglass" <RDouglass@.arisinc.com> wrote in message
> news:ezShnrP3GHA.4024@.TK2MSFTNGP03.phx.gbl...
>> A genius here at work decided to detach a database because the disk the log
>> file was on was full. He copied the log to a different drive and now we
>> cant reattach the db. The db is 260 gig, the log us 70 gig. SQL Server
>> looks like it does the analysis to reattach in about 5 minutes then starts
>> what appears to be a 60 hour recovery cycle.
>> Is there anything I can do to speed this up? How can I truncate a
>> detached log'
>> Any ideas'
>> PLEASE HELP!
>> Richard
>

Detached Database Problem

Hello,
Does anyone know what would cause a database to automatically detach itself
from the server? We have been having a problem the past couple of weeks
where a random set of databases would detach themselves from the server.
TIA,
Dan
Dan,
Something must be detaching the database. Use Profiler to identify who
or what this might be. SQL Server will not do this without instruction.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
solex wrote:
> Hello,
> Does anyone know what would cause a database to automatically detach itself
> from the server? We have been having a problem the past couple of weeks
> where a random set of databases would detach themselves from the server.
> TIA,
> Dan
>

Detached Database Problem

Hello,
Does anyone know what would cause a database to automatically detach itself
from the server? We have been having a problem the past couple of weeks
where a random set of databases would detach themselves from the server.
TIA,
DanDan,
Something must be detaching the database. Use Profiler to identify who
or what this might be. SQL Server will not do this without instruction.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
solex wrote:
> Hello,
> Does anyone know what would cause a database to automatically detach itsel
f
> from the server? We have been having a problem the past couple of weeks
> where a random set of databases would detach themselves from the server.
> TIA,
> Dan
>

Detached Database Problem

Hello,
Does anyone know what would cause a database to automatically detach itself
from the server? We have been having a problem the past couple of weeks
where a random set of databases would detach themselves from the server.
TIA,
DanNo that's not normal. I would put a trace on the database
to determine who or which process is causing this.
>--Original Message--
>Hello,
>Does anyone know what would cause a database to
automatically detach itself
>from the server? We have been having a problem the past
couple of weeks
>where a random set of databases would detach themselves
from the server.
>TIA,
>Dan
>
>.
>|||Dan,
Something must be detaching the database. Use Profiler to identify who
or what this might be. SQL Server will not do this without instruction.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
solex wrote:
> Hello,
> Does anyone know what would cause a database to automatically detach itself
> from the server? We have been having a problem the past couple of weeks
> where a random set of databases would detach themselves from the server.
> TIA,
> Dan
>

detached database disappears?

Hi All:
Running SQL2005 sp1 on Windows Server 2003 R2.
I have Management Studio installed locally on the server. I connected to the
local instance, backed up a database with no problems, and then detached the
database, and now the physical files are gone? not in the "data" folder, not
anywhere...I still have the backup file, but I'm a little freaked out by
this.
Has anyone experience this? I'm logged in as the administrator so I don't
think the files are hidden from view (all the other databases are in the
data folder)
> I have Management Studio installed locally on the server. I connected to
> the local instance, backed up a database with no problems, and then
> detached the database, and now the physical files are gone? not in the
> "data" folder, not anywhere...I still have the backup file, but I'm a
> little freaked out by this.
> Has anyone experience this?
No, I've never seen this. Are you sure you searched all of the drives,
since often we don't place user databases under the program files structure,
but rather on a separate disk altogether.
|||>> I have Management Studio installed locally on the server. I connected to
> No, I've never seen this. Are you sure you searched all of the drives,
> since often we don't place user databases under the program files
> structure, but rather on a separate disk altogether.
Yeah, there's only one drive/partition on the system, and I've searched
everywhere...it's g-o-n-e.
I performed a full dB backup as well as TLog backup before detaching it. I'm
having trouble restoring from the backup now.
First, I tried to restore by typing in the name of the dB in the "to
database" and attaching the backup file. The backup shows the name of the dB
in the backup set, but when I select the "full database backup" and click
OK, I get a strange error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
over the existing "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
to identify an alternate location. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
BUT the database name referenced in the error is NOT the database I'm trying
to restore!
So, I then created a new database with the same name as the one I detached,
and selected "restore" and went through the same process, and I then get a
different error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The backup set holds a backup of a database other than the existing
'EMS_Ultimate' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
3154)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
EMS_Ultimate is the dB I'm trying to restore! And that's the name of the dB
in the backup file...now I'm really nervous!
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||
> Sounds to me like the MDF file is still in the Data folder.
> And the error message references the name of the *file* no the name of the
> database. They do not have to be the same.
>
OK, this is very bizarre...but when I clicked on the "options" in the
"restore database" window, all the settings seemed to be from a previous
restore I did months and months ago on a different database...I don't
understand why those settings would be preserved, when on the first panel
("general") I'm inputting completely different information and selecting a
completely different backup file, but I guess that's one of the mysteries of
the SQL2005.
The errors below were occuring because the MDF and LDF files existed for the
database specified in the options pane...not for the one I was trying to
restore. When I modified the settings in the "options" pane it worked
properly...oh well...at least I have the data back...I was worried the wrong
database was in the backup file.
Thanks for bearing with me!

>
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
>

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
Percy
Sounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scri...p?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy
|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file i
n
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf i
n
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted
.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the se
cond log file to the
first log files name and location? If so, you probably need to go the restor
e route, open a case or
try the last resort posted by Andrew. If not, well, your options are the sam
e...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the D
B
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original locati
on
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a prima
ry
> database file. Could not open new darabase 'crn'. CREATE DATABASE is abort
ed.
> Device activation errror, The physical name 'c:\program files\microsoft sq
l
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

Detached database can't be attached back

Hi
I dettached a database in order to move it to another drive where I have
more space. Original database had 2 datafiles and 2 log files. A data file
and a log file in c:\program files\microsoft sqlserver\mssql\data, called
crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
So, I detached the database using EM: disconnected all users and put the DB
(crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
the other data file and the second log file.
When first tried to attach dastabase through EM it failed to verify all 4
files in the attach dialog. So I copied second log file to original location
with the same old name trying to fool EM. Tried again and it passes
validation and the OK button is enabled, but the process fails: Error 5171
file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
Device activation errror, The physical name 'c:\program files\microsoft sql
server\data\crn_log.ldf ' may be incorrect.
I've also tried:
sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf'
result:
Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
result: Server: Msg 5171, Level 16, State 2, Line 1
C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
primary database file.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'crn'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
'd:\mssql\data\crn_Log2_log.ldf'
I've used the attach/detach process in the past with no problem at all.
Could somebody provide some advice on what could be going on?
Thanks in advance.
PercySounds like you may have corrupted one of the files somehow. Did you take a
FULL backup before detaching? That is a must so you don't run into
situations such as this. Have a look here and see if this helps:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
--
Andrew J. Kelly SQL MVP
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file
> in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the
> DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf
> in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original
> location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a
> primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is
> aborted.
> Device activation errror, The physical name 'c:\program files\microsoft
> sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy|||Are you saying that you deleted one of the log files, and then copied the second log file to the
first log files name and location? If so, you probably need to go the restore route, open a case or
try the last resort posted by Andrew. If not, well, your options are the same...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Percy Cabello" <Percy Cabello@.discussions.microsoft.com> wrote in message
news:952BA145-8E36-43FC-9010-BE2B0E5E14EA@.microsoft.com...
> Hi
> I dettached a database in order to move it to another drive where I have
> more space. Original database had 2 datafiles and 2 log files. A data file
> and a log file in c:\program files\microsoft sqlserver\mssql\data, called
> crn_data.mdf and crn_log.ldf respectively. The other datafile and log file in
> d:\mssql\data called crn_data2_data.ndf and crn_log2_log.ldf respectively.
> So, I detached the database using EM: disconnected all users and put the DB
> (crn) on DBO-only mode, then successfully dettached. The I deleted the ldf in
> the C drive. Then moved the C: mdf datafile to d:\mssql\data together with
> the other data file and the second log file.
> When first tried to attach dastabase through EM it failed to verify all 4
> files in the attach dialog. So I copied second log file to original location
> with the same old name trying to fool EM. Tried again and it passes
> validation and the OK button is enabled, but the process fails: Error 5171
> file c:\program files\microsoft sql server\data\crn_log.ldf is not a primary
> database file. Could not open new darabase 'crn'. CREATE DATABASE is aborted.
> Device activation errror, The physical name 'c:\program files\microsoft sql
> server\data\crn_log.ldf ' may be incorrect.
> I've also tried:
> sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf'
> result:
> Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried: sp_attach_single_file_db 'crn', 'd:\mssql\data\crn_Data.mdf'
> result: Server: Msg 5171, Level 16, State 2, Line 1
> C:\Program Files\Microsoft SQL Server\MSSQL\data\crn_Log.LDF is not a
> primary database file.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'crn'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL\data\crn_Log.LDF' may be incorrect.
> tried:sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> result: sp_attach_db 'crn', 'd:\mssql\data\crn_Data.mdf',
> 'd:\mssql\data\crn_Data2_data.ndf', 'd:\mssql\data\crn_log.ldf',
> 'd:\mssql\data\crn_Log2_log.ldf'
> I've used the attach/detach process in the past with no problem at all.
> Could somebody provide some advice on what could be going on?
> Thanks in advance.
> Percy

detach-copy-attach vs Copy Database Wizard

Instead of running the Copy Database Wizard, if I
1 detached the source database
2 copied the files from the source to the destination server
3 attached the files to the destination server
does it accomplish the same thing? The source will be SQL 7 and the
destination will be SQL 2000, so a database upgrade is involved.
I want to be able to get a copy of the detached .mdf and .ldf files copied
to the destination server that I can use to test the upgrade from SQL 7 to
2000
numerous times if needed. I don't want to use the wizard repeatedly, since
it
requires that the source db be in single user mode or have no users
connected to it.
Thanks,
johnYour method works. You might want to look into BACKUP and RESTORE as =another method to "move" databases. One benefit with this method: you =can use the Transact-SQL command 'BACKUP' to backup your database =without having to take it offline (as you do with detach_db). Another =method is that you can simply come along with your favorite backup =utility and simply backup a file (instead of trying to backup an open =database).
-- Keith
"john" <jgorman@.humanitees.com> wrote in message =news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> > 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> > does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> > I want to be able to get a copy of the detached .mdf and .ldf files =copied
> to the destination server that I can use to test the upgrade from SQL =7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly, =since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> > Thanks,
> john
> >|||Will a restore of a SQL 7 database to a SQL 2000 database result in an
upgrade of that database to SQL 2000?
john
Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
Your method works. You might want to look into BACKUP and RESTORE as
another method to "move" databases. One benefit with this method: you can
use the Transact-SQL command 'BACKUP' to backup your database without having
to take it offline (as you do with detach_db). Another method is that you
can simply come along with your favorite backup utility and simply backup a
file (instead of trying to backup an open database).
--
Keith
"john" <jgorman@.humanitees.com> wrote in message
news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> I want to be able to get a copy of the detached .mdf and .ldf files copied
> to the destination server that I can use to test the upgrade from SQL 7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly,
since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> Thanks,
> john
>|||Yes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"john" <jgorman@.humanitees.com> wrote in message
news:%23tOae2ZmDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Will a restore of a SQL 7 database to a SQL 2000 database result in an
> upgrade of that database to SQL 2000?
> john
>
> Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
> news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Your method works. You might want to look into BACKUP and RESTORE as
> another method to "move" databases. One benefit with this method: you
can
> use the Transact-SQL command 'BACKUP' to backup your database without
having
> to take it offline (as you do with detach_db). Another method is that
you
> can simply come along with your favorite backup utility and simply
backup a
> file (instead of trying to backup an open database).
> --
> Keith
>
> "john" <jgorman@.humanitees.com> wrote in message
> news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Instead of running the Copy Database Wizard, if I
> >
> > 1 detached the source database
> > 2 copied the files from the source to the destination server
> > 3 attached the files to the destination server
> >
> > does it accomplish the same thing? The source will be SQL 7 and the
> > destination will be SQL 2000, so a database upgrade is involved.
> >
> > I want to be able to get a copy of the detached .mdf and .ldf files
copied
> > to the destination server that I can use to test the upgrade from
SQL 7 to
> > 2000
> > numerous times if needed. I don't want to use the wizard
repeatedly,
> since
> > it
> > requires that the source db be in single user mode or have no users
> > connected to it.
> >
> > Thanks,
> > john
> >
> >
>

Detach/Delete

I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot of databases and am wondering if there is some kind of
cleanup I should be doing. I know the Detach leaves the data files intact,
that's why I do it. But does it also leave a lot of crap in the master or
msdb databases?
John
How do you perform the "delete" (I assume you mean DROP DATABASE)? If some the GUI tool then the
extra time might be removal of backup history information in msdb for the database. I doubt that
detach does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>I have noticed that a Detach is almost instant and a Delete can take a few. I have detached a lot
>of databases and am wondering if there is some kind of cleanup I should be doing. I know the
>Detach leaves the data files intact, that's why I do it. But does it also leave a lot of crap in
>the master or msdb databases?
> John
>
|||I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup history data in msdb for that database?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
> How do you perform the "delete" (I assume you mean DROP DATABASE)? If some
> the GUI tool then the extra time might be removal of backup history
> information in msdb for the database. I doubt that detach does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>
|||>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the backup
>history data in msdb for that database?
You don't have to do that, if you don't mind that backup history hanging around... You should have
some job that prunes backup history in any case, so with time history for this database will be
removed.
Whether b-history is removed, well that is easy enough to answer:
1. Read source code for sp_detach_db. I just did, and it doesn't remove backup history.
2. Whether SSMS removes it for you, you can find out by for instance running a Profiler trace when
detaching a database. My guess is that SSMS does not do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the backup
>history data in msdb for that database?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
>
|||I'll have to get rid of the junk when I have bit of time. It doesn't seem
to bother anything performance wise. Nice to keep it tidy though.
Thanks for info Tibor.:-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O8vzmRw7GHA.940@.TK2MSFTNGP03.phx.gbl...
> You don't have to do that, if you don't mind that backup history hanging
> around... You should have some job that prunes backup history in any case,
> so with time history for this database will be removed.
> Whether b-history is removed, well that is easy enough to answer:
> 1. Read source code for sp_detach_db. I just did, and it doesn't remove
> backup history.
> 2. Whether SSMS removes it for you, you can find out by for instance
> running a Profiler trace when detaching a database. My guess is that SSMS
> does not do this for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>

Detach/Delete

I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot of databases and am wondering if there is some kind of
cleanup I should be doing. I know the Detach leaves the data files intact,
that's why I do it. But does it also leave a lot of crap in the master or
msdb databases?
JohnHow do you perform the "delete" (I assume you mean DROP DATABASE)? If some t
he GUI tool then the
extra time might be removal of backup history information in msdb for the da
tabase. I doubt that
detach does this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message
news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>I have noticed that a Detach is almost instant and a Delete can take a few.
I have detached a lot
>of databases and am wondering if there is some kind of cleanup I should be
doing. I know the
>Detach leaves the data files intact, that's why I do it. But does it also
leave a lot of crap in
>the master or msdb databases?
> John
>|||I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup history data in msdb for that database?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
> How do you perform the "delete" (I assume you mean DROP DATABASE)? If some
> the GUI tool then the extra time might be removal of backup history
> information in msdb for the database. I doubt that detach does this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:%23IosITt7GHA.4996@.TK2MSFTNGP04.phx.gbl...
>|||>I use SQL2k5 Management Studio. So every database that I detach, I need to delete the back
up
>history data in msdb for that database?
You don't have to do that, if you don't mind that backup history hanging aro
und... You should have
some job that prunes backup history in any case, so with time history for th
is database will be
removed.
Whether b-history is removed, well that is easy enough to answer:
1. Read source code for sp_detach_db. I just did, and it doesn't remove back
up history.
2. Whether SSMS removes it for you, you can find out by for instance running
a Profiler trace when
detaching a database. My guess is that SSMS does not do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Holt" <johnh@.regionv.k12.mn.us> wrote in message news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.ph
x.gbl...
>I use SQL2k5 Management Studio. So every database that I detach, I need to
delete the backup
>history data in msdb for that database?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u4xObXt7GHA.3604@.TK2MSFTNGP02.phx.gbl...
>|||I'll have to get rid of the junk when I have bit of time. It doesn't seem
to bother anything performance wise. Nice to keep it tidy though.
Thanks for info Tibor.:-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O8vzmRw7GHA.940@.TK2MSFTNGP03.phx.gbl...
> You don't have to do that, if you don't mind that backup history hanging
> around... You should have some job that prunes backup history in any case,
> so with time history for this database will be removed.
> Whether b-history is removed, well that is easy enough to answer:
> 1. Read source code for sp_detach_db. I just did, and it doesn't remove
> backup history.
> 2. Whether SSMS removes it for you, you can find out by for instance
> running a Profiler trace when detaching a database. My guess is that SSMS
> does not do this for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Holt" <johnh@.regionv.k12.mn.us> wrote in message
> news:uvqmkIw7GHA.2364@.TK2MSFTNGP02.phx.gbl...
>