Attaching-detaching Db = Too long

Why does it take time to detach or attach a Db sometimes ?Karolyn,

It shouldn't. It's the fastest method.

The file shoud however be on the local machine, not acorss the network..

How big are we talking?|||It could be the database is going into recovery. Anything showing up in the SQL Errorlog?|||when detaching a database sqlserver checks if there are changes suchs as updates and inserts that were processed but has not (yet) actually done. The changes reside in memory of sqlserver but were not written to the disk. So I think it does these first prior detaching.|||Detaching can take a while, because of the need to "flush the log", etc. It seems to me that the GUI provides a checkbox to skip that step, but that is needless risk from my perspective.

I can't think of a reason that attaching a database would take more than a minute or so (basically modify a few system tables, then go through the normal database recovery/startup that happens whenever a database is first opened).

-PatP

attaching sql 2000 db with enterprse manager

When I upgrade from a sql 7 db to a sql 2000 db, everything goes well. I can also detach the 2000 db with sql 2000 enterprise manager. But when I want to attach the db again to an other sql 2000 server my loginnames disappear when i look at my users. This is because there are not yet any loginnames under the other sql 2000 server. What can I do to get my loginnames also under my usernames at the database to be attached. Else I cannot login in. Tell Me, please!

thanks beforehand.

\greetings ThePercivalYou can use the Transfer Logins DTS function that comes with SQL2K, but that will only transfer the logins and not their associated permissions.

You also have to BCP the syslogins table and then use sp_resolve_logins to restore the associated permissions. See SQL BOL on sp_resolve_logins

Regards,

Hugh Scott

Originally posted by thepercival
When I upgrade from a sql 7 db to a sql 2000 db, everything goes well. I can also detach the 2000 db with sql 2000 enterprise manager. But when I want to attach the db again to an other sql 2000 server my loginnames disappear when i look at my users. This is because there are not yet any loginnames under the other sql 2000 server. What can I do to get my loginnames also under my usernames at the database to be attached. Else I cannot login in. Tell Me, please!

thanks beforehand.

\greetings ThePercival

Attaching MSDB database

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

Attaching & Detaching SQL 2000 Databases

Why is it that sometimes you can detach a database, delete the transaction
log, reattched the database and a new transaction log will be created; and
other times, the attach fails with an error message indicating that the
transaction log file name (including the path) "may be incorrect"?
Thanks,
Ross
Why detach and delete the transaction log? I've seen the
error before when others have done the same - it's not
really the best thing to do.
Some reasons for it not attaching are it not being cleanly
detached, issues or corruption in the database before
detaching, not using the with recovery clause, not using
sp_attach_single_file_db, using sp_attach_single_file_db
when the database had multiple log files.
-Sue
On Mon, 23 Jan 2006 10:37:29 -0600, "Ross Culver"
<rculver@.ranger-systems.com> wrote:

>Why is it that sometimes you can detach a database, delete the transaction
>log, reattched the database and a new transaction log will be created; and
>other times, the attach fails with an error message indicating that the
>transaction log file name (including the path) "may be incorrect"?
>Thanks,
>Ross
>

Attaching & Detaching SQL 2000 Databases

Why is it that sometimes you can detach a database, delete the transaction
log, reattched the database and a new transaction log will be created; and
other times, the attach fails with an error message indicating that the
transaction log file name (including the path) "may be incorrect"?
Thanks,
RossWhy detach and delete the transaction log? I've seen the
error before when others have done the same - it's not
really the best thing to do.
Some reasons for it not attaching are it not being cleanly
detached, issues or corruption in the database before
detaching, not using the with recovery clause, not using
sp_attach_single_file_db, using sp_attach_single_file_db
when the database had multiple log files.
-Sue
On Mon, 23 Jan 2006 10:37:29 -0600, "Ross Culver"
<rculver@.ranger-systems.com> wrote:
>Why is it that sometimes you can detach a database, delete the transaction
>log, reattched the database and a new transaction log will be created; and
>other times, the attach fails with an error message indicating that the
>transaction log file name (including the path) "may be incorrect"?
>Thanks,
>Ross
>

Attaching & Detaching SQL 2000 Databases

Why is it that sometimes you can detach a database, delete the transaction
log, reattched the database and a new transaction log will be created; and
other times, the attach fails with an error message indicating that the
transaction log file name (including the path) "may be incorrect"?
Thanks,
RossWhy detach and delete the transaction log? I've seen the
error before when others have done the same - it's not
really the best thing to do.
Some reasons for it not attaching are it not being cleanly
detached, issues or corruption in the database before
detaching, not using the with recovery clause, not using
sp_attach_single_file_db, using sp_attach_single_file_db
when the database had multiple log files.
-Sue
On Mon, 23 Jan 2006 10:37:29 -0600, "Ross Culver"
<rculver@.ranger-systems.com> wrote:

>Why is it that sometimes you can detach a database, delete the transaction
>log, reattched the database and a new transaction log will be created; and
>other times, the attach fails with an error message indicating that the
>transaction log file name (including the path) "may be incorrect"?
>Thanks,
>Ross
>

Attach/Detach versus Restore/Backup

Hello All,
Given a choice between attach/detach and restore/back up a database(and
subsequently deleting the database) , which one is faster? What I am
interested is, which of the above will release all the memory and other
resources.
Thanks in advance.
Regards,
SoumitraDefine releasing memory and other resources. Memory is
controlled at the server level.
If you delete (nee drop) the DB, you're just deleting it.
Detach does that - it detaches it, but kinda expects to be
re-attached after, say, copying the file. So if your goal
is to delete, I would do a full normal BACKUP and then
delete.
There are also differences in the process: for example, if
using RESTORE, you get your statistics rebuilt.
I would not define things in terms of speed per se if this
is a mission-critical DB you need to do something with.
Speed is certainly a factor, but being down because you
did the wrong thing possibly will negate any speed
differential.
>--Original Message--
>Hello All,
>Given a choice between attach/detach and restore/back up
a database(and
>subsequently deleting the database) , which one is
faster? What I am
>interested is, which of the above will release all the
memory and other
>resources.
>Thanks in advance.
>Regards,
>Soumitra
>
>.
>|||Backup/Restore advantages:
1. Backup you can do on-line while Detach is off-line only operation
2. Backup will create file which has less size than original DB
3. Backup will contain the whole DB (DB+Log) while with Detach you will have
separate file for DB and Log.
4. With backup you can check integrity to be sure file iz not corrupted
after actual backup.
Detach advantages:
1. It is faster to move DB with Detach than with backup - you will need just
copy files and don't need to spend a time on a backup operation.
2. The files will contain current DB and current Transaction log while in
case of full backup you will loose current Transaction Log - it is important
in case of emergency situation with replication.
Regards.
---
All information provided above AS IS.
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> Given a choice between attach/detach and restore/back up a database(and
> subsequently deleting the database) , which one is faster? What I am
> interested is, which of the above will release all the memory and other
> resources.
> Thanks in advance.
> Regards,
> Soumitra
>|||Thanks Guys.
One question that remains is how can one tell SQL Server to release the
memory it holds onto? Let's say I create 1000 databases in a single instance
of SQl Server/MSDE. At the end of this the memory usage let say is 200 MB.
Now out of the 1000 databases, the first 100 databases are not used and
there is no user connected to it.
Is there any way to get back the momory associated with the first 100
databases?
Thanks.
Regards,
Soumitra
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> Given a choice between attach/detach and restore/back up a database(and
> subsequently deleting the database) , which one is faster? What I am
> interested is, which of the above will release all the memory and other
> resources.
> Thanks in advance.
> Regards,
> Soumitra
>|||Hi Soumitra
You just drop the databases which are not in use. For
precautionary steps , just have backup of the databases
which you will be DROPING , move these backup files to
Tape or some secured place .
Regards
Suri
>--Original Message--
>Thanks Guys.
>One question that remains is how can one tell SQL Server
to release the
>memory it holds onto? Let's say I create 1000 databases
in a single instance
>of SQl Server/MSDE. At the end of this the memory usage
let say is 200 MB.
>Now out of the 1000 databases, the first 100 databases
are not used and
>there is no user connected to it.
>Is there any way to get back the momory associated with
the first 100
>databases?
>Thanks.
>Regards,
>Soumitra
>"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote
in message
>news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
>> Hello All,
>> Given a choice between attach/detach and restore/back
up a database(and
>> subsequently deleting the database) , which one is
faster? What I am
>> interested is, which of the above will release all the
memory and other
>> resources.
>> Thanks in advance.
>> Regards,
>> Soumitra
>>
>
>.
>|||Soumitra
Why are you so worried about releasing the memory? SQL
Server will use all the memory it needs from the amount
that is available . It will release it if another process
requires it, so usually it is not a problem. It can
sometimes be unpredictable when sharing a server with non
SQL Server applications, so it is best practice to not run
anything but SQL Server on your server.
If you are running another application and are having
memory problems you can always set SQL Server to use fixed
memory. You may slow the performance of your SQL Server by
doing this.
Hope this helps.
John|||"Allan Hirt" <allanh@.avanade.com> wrote in message
news:02f301c3a7e2$5dab22d0$a601280a@.phx.gbl...
> Detach does that - it detaches it, but kinda expects to be
> re-attached after, say, copying the file. So if your goal
> is to delete, I would do a full normal BACKUP and then
> delete.
In what way does it "expect" to be re-attached?
References to the detached database are removed. There is no expectancy.
Never mind the theological debate about whether or not a piece of software
CAN expect something ;)

attach/detach db by user with no priviliges

Hello,
I am looking for a way to have users with normal security (= not system
admin and so on) to attach and detach a specific database. Maybe via a
stored proc. Does anyone have an idea?
Thank you
Frank"Frank" schrieb:
> Hello,
> I am looking for a way to have users with normal security (= not system
> admin and so on) to attach and detach a specific database. Maybe via a
> stored proc. Does anyone have an idea?
> Thank you
> Frank
If the users are members of the dbcreator server role they can do it
(symin is not necessary).
If the users are not supposed to be member of any serverroles then I see no
solution - the right to create a db is needed ... (of xourse you could also
to give the users statement permission to create a db in the master, and the
n
write a stored proc that takes the same parameters like sp_attach_db and
executes the sp_attach_db for the user - watch out for a broken ownership
chain! But if you are ready to go that far, you can also add them to the
dbcreators server role.)

Attach/Detach database

I need to copy my SQL Server 2000 database from my development machine to
the server of one of the client. By using Enterprise Manager I was going to
detach db from my server, create a database with the same name on a new
server, detach it, replace files on the new server with my copies and attach
both databases on both servers back.
Unfortunately my server displays Attach Database entry only in popup menu. I
have nothing to attach yet.
I did this operation several times and had no problem before. Probably I
detached some of the unimportant databases and forgot to attach it back.
How can I refresh (clean up whatever) Enterprise Manager memory about last
operation in order to have the ability to use Detach feature?
Thank you
Al
Hi
I would recommend that you do this from Query Analyser, there is no need to
create the database first.
http://support.microsoft.com/default...en-us%3b224071
John
"vul" <aaa@.optonline.net> wrote in message
news:enYHb8trEHA.3900@.TK2MSFTNGP10.phx.gbl...
> I need to copy my SQL Server 2000 database from my development machine to
> the server of one of the client. By using Enterprise Manager I was going
to
> detach db from my server, create a database with the same name on a new
> server, detach it, replace files on the new server with my copies and
attach
> both databases on both servers back.
> Unfortunately my server displays Attach Database entry only in popup menu.
I
> have nothing to attach yet.
> I did this operation several times and had no problem before. Probably I
> detached some of the unimportant databases and forgot to attach it back.
> How can I refresh (clean up whatever) Enterprise Manager memory about last
> operation in order to have the ability to use Detach feature?
> Thank you
> Al
>
|||The detach option is context-sensitive so you need to first select the
desired database in the tree.
You should not create a new database before attaching. Detach the database
and copy the files to the new server. In the Attach database dialog,
specify the mdf file, click Verify and specify the desired database name in
the 'Attach As' box. You can change the locations of the other files in the
list, if needed.
You might also consider using the sp_attach_db and sp_detach_db stored
procedures. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"vul" <aaa@.optonline.net> wrote in message
news:enYHb8trEHA.3900@.TK2MSFTNGP10.phx.gbl...
>I need to copy my SQL Server 2000 database from my development machine to
> the server of one of the client. By using Enterprise Manager I was going
> to
> detach db from my server, create a database with the same name on a new
> server, detach it, replace files on the new server with my copies and
> attach
> both databases on both servers back.
> Unfortunately my server displays Attach Database entry only in popup menu.
> I
> have nothing to attach yet.
> I did this operation several times and had no problem before. Probably I
> detached some of the unimportant databases and forgot to attach it back.
> How can I refresh (clean up whatever) Enterprise Manager memory about last
> operation in order to have the ability to use Detach feature?
> Thank you
> Al
>

Attach/Detach database

I need to copy my SQL Server 2000 database from my development machine to
the server of one of the client. By using Enterprise Manager I was going to
detach db from my server, create a database with the same name on a new
server, detach it, replace files on the new server with my copies and attach
both databases on both servers back.
Unfortunately my server displays Attach Database entry only in popup menu. I
have nothing to attach yet.
I did this operation several times and had no problem before. Probably I
detached some of the unimportant databases and forgot to attach it back.
How can I refresh (clean up whatever) Enterprise Manager memory about last
operation in order to have the ability to use Detach feature?
Thank you
AlHi
I would recommend that you do this from Query Analyser, there is no need to
create the database first.
http://support.microsoft.com/defaul...ben-us%3b224071
John
"vul" <aaa@.optonline.net> wrote in message
news:enYHb8trEHA.3900@.TK2MSFTNGP10.phx.gbl...
> I need to copy my SQL Server 2000 database from my development machine to
> the server of one of the client. By using Enterprise Manager I was going
to
> detach db from my server, create a database with the same name on a new
> server, detach it, replace files on the new server with my copies and
attach
> both databases on both servers back.
> Unfortunately my server displays Attach Database entry only in popup menu.
I
> have nothing to attach yet.
> I did this operation several times and had no problem before. Probably I
> detached some of the unimportant databases and forgot to attach it back.
> How can I refresh (clean up whatever) Enterprise Manager memory about last
> operation in order to have the ability to use Detach feature?
> Thank you
> Al
>|||The detach option is context-sensitive so you need to first select the
desired database in the tree.
You should not create a new database before attaching. Detach the database
and copy the files to the new server. In the Attach database dialog,
specify the mdf file, click Verify and specify the desired database name in
the 'Attach As' box. You can change the locations of the other files in the
list, if needed.
You might also consider using the sp_attach_db and sp_detach_db stored
procedures. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"vul" <aaa@.optonline.net> wrote in message
news:enYHb8trEHA.3900@.TK2MSFTNGP10.phx.gbl...
>I need to copy my SQL Server 2000 database from my development machine to
> the server of one of the client. By using Enterprise Manager I was going
> to
> detach db from my server, create a database with the same name on a new
> server, detach it, replace files on the new server with my copies and
> attach
> both databases on both servers back.
> Unfortunately my server displays Attach Database entry only in popup menu.
> I
> have nothing to attach yet.
> I did this operation several times and had no problem before. Probably I
> detached some of the unimportant databases and forgot to attach it back.
> How can I refresh (clean up whatever) Enterprise Manager memory about last
> operation in order to have the ability to use Detach feature?
> Thank you
> Al
>

attach/ detach of dbs

hi,
Is it possible to attach and detach databases
participating in replication?
regards,
bharath| hi,
|
| Is it possible to attach and detach databases
| participating in replication?
|
| regards,
| bharath
--
Hi Bharath,
From SQL Server books online:
"You cannot detach a database while it is being replicated."
May I ask what your specific requirements are?
--
Eric Cárdenas
SQL Server support|||HiEric,
The requirement is simple. I need to pass on the physical data and log files of one database participating in replication to another server( managed by another group). I do not want other databases to be brought down as well (shut down of sql). That is why this question arose...
>--Original Message--
>| hi,
>| >| Is it possible to attach and detach databases >| participating in replication?
>| >| regards,
>| bharath
>--
>Hi Bharath,
>From SQL Server books online:
>"You cannot detach a database while it is being replicated."
>May I ask what your specific requirements are?
>--
>Eric C=E1rdenas
>SQL Server support
>.
>||||
| HiEric,
| The requirement is simple. I need to pass on the physical
| data and log files of one database participating in
| replication to another server( managed by another group).
| I do not want other databases to be brought down as well
| (shut down of sql). That is why this question arose...
--
Hi Bharath,
How about backing up the subscriber/publisher database participating in the
replication? You can then restore that backup to another server and you
have the same data. The mdf/ldf will be created for you when you restore.
Would this be a suitable workaround for you?
Hope this helps,
--
Eric Cárdenas
SQL Server support

Attach without Detach?

Is it possible to copy the .mdf and .ldf files from a production server to
another server and then attach the database without detaching the database
from production? I was thinking this might replace the copying of the .BAK
and the restore.
Thanks.
Ron
Mostly it will work, but might not as reliable as first detach. Also, the
backup and restore leaves you another running db.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron
|||That is a good way if you don't care about the integrity of your
database<g>. Seriously you should never simply copy the files. It only
takes a second to issue the detach and it will be done right. If you can't
take it offline then do a full backup instead.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron
|||If one database is live on server, you can not copy its physical files.. this
will lead into file sharing voilation. Before copying files to another
location you must have to detach a database first.
"sql question" wrote:

> Mostly it will work, but might not as reliable as first detach. Also, the
> backup and restore leaves you another running db.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
>
>
|||Thank You for the replies
"Deepakontheweb" wrote:
[vbcol=seagreen]
> If one database is live on server, you can not copy its physical files.. this
> will lead into file sharing voilation. Before copying files to another
> location you must have to detach a database first.
>
>
> "sql question" wrote:

Attach without Detach?

Is it possible to copy the .mdf and .ldf files from a production server to
another server and then attach the database without detaching the database
from production? I was thinking this might replace the copying of the .BAK
and the restore.
Thanks.
RonMostly it will work, but might not as reliable as first detach. Also, the
backup and restore leaves you another running db.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||That is a good way if you don't care about the integrity of your
database<g>. Seriously you should never simply copy the files. It only
takes a second to issue the detach and it will be done right. If you can't
take it offline then do a full backup instead.
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||If one database is live on server, you can not copy its physical files.. this
will lead into file sharing voilation. Before copying files to another
location you must have to detach a database first.
"sql question" wrote:
> Mostly it will work, but might not as reliable as first detach. Also, the
> backup and restore leaves you another running db.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> > Is it possible to copy the .mdf and .ldf files from a production server to
> > another server and then attach the database without detaching the database
> > from production? I was thinking this might replace the copying of the
> > .BAK
> > and the restore.
> >
> > Thanks.
> >
> > Ron
>
>|||Thank You for the replies
"Deepakontheweb" wrote:
> If one database is live on server, you can not copy its physical files.. this
> will lead into file sharing voilation. Before copying files to another
> location you must have to detach a database first.
>
>
> "sql question" wrote:
> > Mostly it will work, but might not as reliable as first detach. Also, the
> > backup and restore leaves you another running db.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> > > Is it possible to copy the .mdf and .ldf files from a production server to
> > > another server and then attach the database without detaching the database
> > > from production? I was thinking this might replace the copying of the
> > > .BAK
> > > and the restore.
> > >
> > > Thanks.
> > >
> > > Ron
> >
> >
> >

Attach without Detach?

Is it possible to copy the .mdf and .ldf files from a production server to
another server and then attach the database without detaching the database
from production? I was thinking this might replace the copying of the .BAK
and the restore.
Thanks.
RonMostly it will work, but might not as reliable as first detach. Also, the
backup and restore leaves you another running db.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||That is a good way if you don't care about the integrity of your
database<g>. Seriously you should never simply copy the files. It only
takes a second to issue the detach and it will be done right. If you can't
take it offline then do a full backup instead.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||If one database is live on server, you can not copy its physical files.. thi
s
will lead into file sharing voilation. Before copying files to another
location you must have to detach a database first.
"sql question" wrote:

> Mostly it will work, but might not as reliable as first detach. Also, the
> backup and restore leaves you another running db.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
>
>|||Thank You for the replies
"Deepakontheweb" wrote:
[vbcol=seagreen]
> If one database is live on server, you can not copy its physical files.. t
his
> will lead into file sharing voilation. Before copying files to another
> location you must have to detach a database first.
>
>
> "sql question" wrote:
>

attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
Tom
On Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent
|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.

> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Steen Schlter Persson (DK) wrote:[vbcol=seagreen]
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).

attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.

> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Steen Schlter Persson (DK) wrote:
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...[vbcol=seagreen]
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).

attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Steen Schlüter Persson (DK) wrote:
> tshad wrote:
>> When moving all yous database to a new server where you install Sql
>> Server, is it better to use detach, copy database to new server than
>> attach it or backup on the old server and restore the backup.
>> I assume that I don't really need to move the Master or do I?
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).

Attach Question

Hello,
I'm having problems removing replication components from a db I need to
detach and move to a different server. The db won't be using replication on
the new server. Since all replication components aren't removed Ent Mgr
won't let me detach. Can I just stop the SQL services, copy the .mdf to the
new server, attach and cleanup replication there?
Any help appreciated!
Thanks in advance.Hi Mark
Taking a backup will be better than stopping the server.
You can remove replication using sp_removedbreplication and possibly
sp_dropdistributor see the topic "How to: Disable Publishing and
Distribution" in Books Online
John
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:0EA3E109-446C-4337-963C-1B7C120F3F52@.microsoft.com...
> Hello,
> I'm having problems removing replication components from a db I need to
> detach and move to a different server. The db won't be using replication
> on
> the new server. Since all replication components aren't removed Ent Mgr
> won't let me detach. Can I just stop the SQL services, copy the .mdf to
> the
> new server, attach and cleanup replication there?
> Any help appreciated!
> Thanks in advance.