Asp.net / sql help: detaching

Showing posts with label detaching. Show all posts

Thursday, March 29, 2012

attach detach db

Is it possible detaching one db coping under/ different names /master and lof
files and reattaching db again to cause login problems later. if yes why?Not sure I understand the question but the answer is most likely in one of
these:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi,
If you are attaching the database with different name in same server, you
will never get any Login issue. If you are attaching into
a different server then you have syncronize the Logins and users using the
system procedure SP_CHANGE_USERS_LOGIN (see Books online)
Thanks
Hari
SQL Server MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> Is it possible detaching one db coping under/ different names /master and
> lof
> files and reattaching db again to cause login problems later. if yes why?
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Hari Prasad" wrote:
> Hi,
> If you are attaching the database with different name in same server, you
> will never get any Login issue. If you are attaching into
> a different server then you have syncronize the Logins and users using the
> system procedure SP_CHANGE_USERS_LOGIN (see Books online)
> Thanks
> Hari
> SQL Server MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Hi Hary,
thanks for your reply. Let me try to explain the situation I am in. I had to
make copy for training purposes of a production db. I detached prod db and
copied mdf and ldf in the same data directory but with names test.mdf and
test.ldf. Then I attached our prod db and new test db. During attaching I had
to choose dbowner (domain admin). It seemed to me that i saved time using
this teqniche. But later users reported that they couldnt log in prod and
test db via the application (its in citrix). After short phone talk with
vendors they explained that detaching and attaching caused dbo alias
deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
'dbo' and everithing looks fine with prod db. But its impossible to use test
db. Users that are in sys admin group dont have problem but ordinary users
that connect to applications db via app user cannot login at all. I wonder
whats the problem? all ordinary users are stored in users table in prod db
and have encrypted passwords. app user password is encrypted and stored in
different db.
i will be dismissed : )
"Andrew J. Kelly" wrote:
> Not sure I understand the question but the answer is most likely in one of
> these:
>
> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
> system DB's
> http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
> http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
> to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> Restore
> http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> users
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> > Is it possible detaching one db coping under/ different names /master and
> > lof
> > files and reattaching db again to cause login problems later. if yes why?
> >
>
>|||Then you should look at the links I posted. In particular this first one
should be most helpful:
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> Hi Hary,
> thanks for your reply. Let me try to explain the situation I am in. I had
> to
> make copy for training purposes of a production db. I detached prod db and
> copied mdf and ldf in the same data directory but with names test.mdf and
> test.ldf. Then I attached our prod db and new test db. During attaching I
> had
> to choose dbowner (domain admin). It seemed to me that i saved time using
> this teqniche. But later users reported that they couldnt log in prod and
> test db via the application (its in citrix). After short phone talk with
> vendors they explained that detaching and attaching caused dbo alias
> deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> 'dbo' and everithing looks fine with prod db. But its impossible to use
> test
> db. Users that are in sys admin group dont have problem but ordinary users
> that connect to applications db via app user cannot login at all. I wonder
> whats the problem? all ordinary users are stored in users table in prod db
> and have encrypted passwords. app user password is encrypted and stored in
> different db.
> i will be dismissed : )
>
> "Andrew J. Kelly" wrote:
>> Not sure I understand the question but the answer is most likely in one
>> of
>> these:
>>
>> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> http://www.databasejournal.com/features/mssql/article.php/3379901
>> Moving
>> system DB's
>> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> Servers
>> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> Databases
>> to a New Location with Detach/Attach
>> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
>> Restore
>> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> and
>> Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> > Is it possible detaching one db coping under/ different names /master
>> > and
>> > lof
>> > files and reattaching db again to cause login problems later. if yes
>> > why?
>> >
>>|||Thanx : ) I hope this will help. Just curios if EE could have the same login
problems. May be will be better to upgrade to EE
"Andrew J. Kelly" wrote:
> Then you should look at the links I posted. In particular this first one
> should be most helpful:
> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> Issues When a Database Is Moved Between SQL Servers
> --
> Andrew J. Kelly SQL MVP
>
> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
> > Hi Hary,
> > thanks for your reply. Let me try to explain the situation I am in. I had
> > to
> > make copy for training purposes of a production db. I detached prod db and
> > copied mdf and ldf in the same data directory but with names test.mdf and
> > test.ldf. Then I attached our prod db and new test db. During attaching I
> > had
> > to choose dbowner (domain admin). It seemed to me that i saved time using
> > this teqniche. But later users reported that they couldnt log in prod and
> > test db via the application (its in citrix). After short phone talk with
> > vendors they explained that detaching and attaching caused dbo alias
> > deleting. They have app login that use dbo alias. I ran sp_addalias 'app,
> > 'dbo' and everithing looks fine with prod db. But its impossible to use
> > test
> > db. Users that are in sys admin group dont have problem but ordinary users
> > that connect to applications db via app user cannot login at all. I wonder
> > whats the problem? all ordinary users are stored in users table in prod db
> > and have encrypted passwords. app user password is encrypted and stored in
> > different db.
> >
> > i will be dismissed : )
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Not sure I understand the question but the answer is most likely in one
> >> of
> >> these:
> >>
> >>
> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
> >> http://www.databasejournal.com/features/mssql/article.php/3379901
> >> Moving
> >> system DB's
> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
> >> Servers
> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
> >> Databases
> >> to a New Location with Detach/Attach
> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in a
> >> Restore
> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
> >> and
> >> Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
> >> after a
> >> Restore
> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
> >> users
> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
> >> Permission
> >> Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
> >> Issues When a Database Is Moved Between SQL Servers
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >> Restoring a .mdf
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
> >> > Is it possible detaching one db coping under/ different names /master
> >> > and
> >> > lof
> >> > files and reattaching db again to cause login problems later. if yes
> >> > why?
> >> >
> >>
> >>
> >>
>
>|||It would be the same for any edition.
--
Andrew J. Kelly SQL MVP
"Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
news:B7FD797D-F1DB-41E4-B6D4-0BDA27B27153@.microsoft.com...
> Thanx : ) I hope this will help. Just curios if EE could have the same
> login
> problems. May be will be better to upgrade to EE
> "Andrew J. Kelly" wrote:
>> Then you should look at the links I posted. In particular this first one
>> should be most helpful:
>> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> after a
>> Restore
>> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> Permission
>> Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues When a Database Is Moved Between SQL Servers
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> news:62CB085D-5980-4776-8A35-DEAC178F2AEB@.microsoft.com...
>> > Hi Hary,
>> > thanks for your reply. Let me try to explain the situation I am in. I
>> > had
>> > to
>> > make copy for training purposes of a production db. I detached prod db
>> > and
>> > copied mdf and ldf in the same data directory but with names test.mdf
>> > and
>> > test.ldf. Then I attached our prod db and new test db. During attaching
>> > I
>> > had
>> > to choose dbowner (domain admin). It seemed to me that i saved time
>> > using
>> > this teqniche. But later users reported that they couldnt log in prod
>> > and
>> > test db via the application (its in citrix). After short phone talk
>> > with
>> > vendors they explained that detaching and attaching caused dbo alias
>> > deleting. They have app login that use dbo alias. I ran sp_addalias
>> > 'app,
>> > 'dbo' and everithing looks fine with prod db. But its impossible to use
>> > test
>> > db. Users that are in sys admin group dont have problem but ordinary
>> > users
>> > that connect to applications db via app user cannot login at all. I
>> > wonder
>> > whats the problem? all ordinary users are stored in users table in prod
>> > db
>> > and have encrypted passwords. app user password is encrypted and stored
>> > in
>> > different db.
>> >
>> > i will be dismissed : )
>> >
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Not sure I understand the question but the answer is most likely in
>> >> one
>> >> of
>> >> these:
>> >>
>> >>
>> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
>> >> http://www.databasejournal.com/features/mssql/article.php/3379901
>> >> Moving
>> >> system DB's
>> >> http://www.support.microsoft.com/?id=314546 Moving DB's between
>> >> Servers
>> >> http://www.support.microsoft.com/?id=224071 Moving SQL Server
>> >> Databases
>> >> to a New Location with Detach/Attach
>> >> http://support.microsoft.com/?id=221465 Using WITH MOVE in
>> >> a
>> >> Restore
>> >> http://www.support.microsoft.com/?id=246133 How To Transfer Logins
>> >> and
>> >> Passwords Between SQL Servers
>> >> http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs
>> >> after a
>> >> Restore
>> >> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins
>> >> to
>> >> users
>> >> http://www.support.microsoft.com/?id=168001 User Logon and/or
>> >> Permission
>> >> Errors After Restoring Dump
>> >> http://www.support.microsoft.com/?id=240872 How to Resolve
>> >> Permission
>> >> Issues When a Database Is Moved Between SQL Servers
>> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> >> Restoring a .mdf
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Eli Milkova" <EliMilkova@.discussions.microsoft.com> wrote in message
>> >> news:8BD9FBF7-6420-49C7-8B3F-25E0774BA43B@.microsoft.com...
>> >> > Is it possible detaching one db coping under/ different names
>> >> > /master
>> >> > and
>> >> > lof
>> >> > files and reattaching db again to cause login problems later. if yes
>> >> > why?
>> >> >
>> >>
>> >>
>> >>
>>sql

Attach DB Without Transaction Logs

Hi, there,

I have multiple transaction log files. I don't need the transaction logs and intended to remove them. I have tried detaching the database, rename the logs and trying to re-attach the database without the logs but to no avail. The error message mentioned something like file activation failed and SQL couldn't rebuild the transaction logs.

Is there a way to get rid of the transaction log files?

Thank you.

Regards,

Yong Hwee

Hi,

you can use sp_attach_single_file_db but it works only on databases that have a single log file.

When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

...

OR by reading this thread attaching DB without .ldf file ?

tosc

|||

Hi, Tosc,

Thank you for your immediate reply. It seems like there is no workaround for database with multiple log files.

Regards,

Yong Hwee

|||

There are lot of limitation with sp_attach_single_file_db. The database should not have morethan one LOG and Datafile and the database should be properly detached. So in your case this process will not do. You requirement is to remove the Secondary LOG File, if am correct. What you can do is, Remove the secondary log file first using

DBCC SHRINKFILE (,EMPTYFILE)
alter database test remove file .

Then you can trucate the primary log file to any size you want. Please take backup of the database before starting anything

Madhu

Refer : http://madhuottapalam.blogspot.com/search?q=Transaction+Log

Madhu

|||

In SQL Server 2005 you can you use CREATE DATABASE syntax to rebuild the log. The ATTACH_REBUILD_LOG syntax should work even if there are multiple log files.

|||

Hi, there,

Thank you for your reply. I managed to delete the log by first backup with option to truncate log after which I change the database to simple recovery. I can delete the logs this way.

Thank you.

Regards,

Yong Hwee

Tuesday, March 27, 2012

attach databases without detaching

If i have all the mdf and ldf files that comprise of a database, is it
guaranteed that i can always attach the db even if they are not successfully
detached
The only ways i know of a successful attach are if the databases are
detached successfully and if SQL is gracefully shut down.
There are so many 3rd party tools that replicate these files asynchronously
or have these files mirrored on a bit level,etc... and then they just stop
the replication or mirroring and claim they can attach it... Will this work
all the time ?
Or lets put it this way... Under what conditions will attach not work even
if i have all the relevant data and log files
If SQL Server has been shut down and the files copied or they were dettached
properly there shouldn't be too much of a problem. But if they simply
attempt to copy the files while SQL Server is running there is not telling
what they will get.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>
|||If you did not detach the database, there is no guarantee that an attach
will always work, even if you shut down the database and copy the data and
log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>
|||echoing Andy and Peter, if you have multiple log files for a single DB it is
almost guranteed that an attach will not work without a prior detach.
"Peter Yeoh" wrote:

> If you did not detach the database, there is no guarantee that an attach
> will always work, even if you shut down the database and copy the data and
> log files.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> successfully
> asynchronously
> work
>
>
sql

attach databases without detaching

If i have all the mdf and ldf files that comprise of a database, is it
guaranteed that i can always attach the db even if they are not successfully
detached
The only ways i know of a successful attach are if the databases are
detached successfully and if SQL is gracefully shut down.
There are so many 3rd party tools that replicate these files asynchronously
or have these files mirrored on a bit level,etc... and then they just stop
the replication or mirroring and claim they can attach it... Will this work
all the time ?
Or lets put it this way... Under what conditions will attach not work even
if i have all the relevant data and log filesIf SQL Server has been shut down and the files copied or they were dettached
properly there shouldn't be too much of a problem. But if they simply
attempt to copy the files while SQL Server is running there is not telling
what they will get.
--
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||If you did not detach the database, there is no guarantee that an attach
will always work, even if you shut down the database and copy the data and
log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||echoing Andy and Peter, if you have multiple log files for a single DB it is
almost guranteed that an attach will not work without a prior detach.
"Peter Yeoh" wrote:
> If you did not detach the database, there is no guarantee that an attach
> will always work, even if you shut down the database and copy the data and
> log files.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> > If i have all the mdf and ldf files that comprise of a database, is it
> > guaranteed that i can always attach the db even if they are not
> successfully
> > detached
> >
> > The only ways i know of a successful attach are if the databases are
> > detached successfully and if SQL is gracefully shut down.
> >
> > There are so many 3rd party tools that replicate these files
> asynchronously
> > or have these files mirrored on a bit level,etc... and then they just stop
> > the replication or mirroring and claim they can attach it... Will this
> work
> > all the time ?
> >
> > Or lets put it this way... Under what conditions will attach not work even
> > if i have all the relevant data and log files
> >
> >
>
>

attach databases without detaching

If i have all the mdf and ldf files that comprise of a database, is it
guaranteed that i can always attach the db even if they are not successfully
detached
The only ways i know of a successful attach are if the databases are
detached successfully and if SQL is gracefully shut down.
There are so many 3rd party tools that replicate these files asynchronously
or have these files mirrored on a bit level,etc... and then they just stop
the replication or mirroring and claim they can attach it... Will this work
all the time ?
Or lets put it this way... Under what conditions will attach not work even
if i have all the relevant data and log filesIf SQL Server has been shut down and the files copied or they were dettached
properly there shouldn't be too much of a problem. But if they simply
attempt to copy the files while SQL Server is running there is not telling
what they will get.
Andrew J. Kelly SQL MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||If you did not detach the database, there is no guarantee that an attach
will always work, even if you shut down the database and copy the data and
log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> If i have all the mdf and ldf files that comprise of a database, is it
> guaranteed that i can always attach the db even if they are not
successfully
> detached
> The only ways i know of a successful attach are if the databases are
> detached successfully and if SQL is gracefully shut down.
> There are so many 3rd party tools that replicate these files
asynchronously
> or have these files mirrored on a bit level,etc... and then they just stop
> the replication or mirroring and claim they can attach it... Will this
work
> all the time ?
> Or lets put it this way... Under what conditions will attach not work even
> if i have all the relevant data and log files
>|||echoing Andy and Peter, if you have multiple log files for a single DB it is
almost guranteed that an attach will not work without a prior detach.
"Peter Yeoh" wrote:

> If you did not detach the database, there is no guarantee that an attach
> will always work, even if you shut down the database and copy the data and
> log files.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:O1NLrfIkEHA.384@.TK2MSFTNGP10.phx.gbl...
> successfully
> asynchronously
> work
>
>

Sunday, March 25, 2012

Attach database

Hi folks
I have copied a .mdf file from machine1 (without detaching it)
Can I attach it to a SQL Server which is running on a different machine
(machine2)? If not, is there anyother way to restore the database with
the .mdf file (without using the backup)?
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I gather you stopped the SQL service first to do the copy without detaching
the database.
I would recommend using a backup but if you want to do it this way you can
use EM, right click on databases > attach database
OR TSQL
EXEC sp_attach_db 'MyDb', 'C:\MyDirectory\MyDb_Data.mdf',
'C:\MyDirectory\MyDb_Log.ldf'
Andy
"Venkatesan M" wrote:

> Hi folks
> I have copied a .mdf file from machine1 (without detaching it)
> Can I attach it to a SQL Server which is running on a different machine
> (machine2)? If not, is there anyother way to restore the database with
> the .mdf file (without using the backup)?
> Thanks in advance.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>|||The only way to gaurantee an attach is when the db files are properly
detached first (without open tran). In your case you could try with
e.g.
sp_attach_single_file_db 'db_name',N'mdf file path'
-oj
"Venkatesan M" <venkat_asp@.yahoo.co.uk> wrote in message
news:%23FJfMrDBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi folks
> I have copied a .mdf file from machine1 (without detaching it)
> Can I attach it to a SQL Server which is running on a different machine
> (machine2)? If not, is there anyother way to restore the database with
> the .mdf file (without using the backup)?
> Thanks in advance.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Thursday, March 29, 2012

attach detach db

attach detach db

attach detach db

Attach DB Without Transaction Logs

Tuesday, March 27, 2012

attach databases without detaching

attach databases without detaching

attach databases without detaching

Sunday, March 25, 2012

Attach database

Asp.net / sql help

Blog Archive

About Me