Friday, February 24, 2012

About sp_detach_db and sp_attach_db

Hello there,
Our database data and log files were stored on UNC directory on different
server, we are thinking to use sp_detach_db and sp_attach_db to move data and
log files from UNC to local disk. Becasuse we are not familiar about
application, any minor issue could stop applicaiton. Is there any issue could
happen on sp_detach_db and sp_attach_db?
Thanks very much
Lionel
I haven't had any problems using them properly (that is, attaching databases
with sp_attach_db that were detached using sp_detach_db), but that doesn't
mean there is no possibility of issues. As with anything else, back up
first just to be on the safe side.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> Hello there,
> Our database data and log files were stored on UNC directory on different
> server, we are thinking to use sp_detach_db and sp_attach_db to move data
and
> log files from UNC to local disk. Becasuse we are not familiar about
> application, any minor issue could stop applicaiton. Is there any issue
could
> happen on sp_detach_db and sp_attach_db?
> Thanks very much
> Lionel
>
|||Thanks for your reply, One of the further questions is that I noticed one of
our database owner is "unknow", I wonder why it would be happed.
when we use sp_attach_db to change the database owner from "unknow" to "sa",
any issue could be?
Thanks again
Lionel
"Adam Machanic" wrote:

> I haven't had any problems using them properly (that is, attaching databases
> with sp_attach_db that were detached using sp_detach_db), but that doesn't
> mean there is no possibility of issues. As with anything else, back up
> first just to be on the safe side.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "lionel" <lionel@.discussions.microsoft.com> wrote in message
> news:3E57A4A7-8D73-4DD2-B6AB-562772BF86EA@.microsoft.com...
> and
> could
>
>
|||You can restore user and owner settings on the target server using
sp_change_users_login and sp_changedbowner.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...
> Thanks for your reply, One of the further questions is that I noticed one
of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
"sa",[vbcol=seagreen]
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:
databases[vbcol=seagreen]
doesn't[vbcol=seagreen]
different[vbcol=seagreen]
data[vbcol=seagreen]
issue[vbcol=seagreen]
|||Hi,
Before detaching, I recommend you to do a Full database backup of your
database. So even if Attach wont work you
could use the backup to recover the database to local hard disk using
RESTORE DATABASE command.
After that as Adam suggested you could use sp_changedbowner to change the
database owner
Thanks
Hari
SQL Server MVP
"lionel" <lionel@.discussions.microsoft.com> wrote in message
news:2220E2B9-D439-407A-9DE3-3D18AF490F46@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply, One of the further questions is that I noticed one
> of
> our database owner is "unknow", I wonder why it would be happed.
> when we use sp_attach_db to change the database owner from "unknow" to
> "sa",
> any issue could be?
> Thanks again
> Lionel
>
> "Adam Machanic" wrote:

No comments:

Post a Comment