I was testing a restore using a backup of db1 and
restoring to db2. Both db1 and db2 already exist and have
the same file structure. The restore was also moving the
db1 files to the db2 file locations. I did not specify
with replace. I expected the restore to fail, but it
didn't. What am I missing?Through a job in EM. Below is sql.
restore database SynComp_219
from disk = '\\hfddbms1p\d-
drive\mssql\backup\synygy_1t_1206a_0711.bak'
with
move 'metadata_001' to 'D:\MSSQL$INST1
\Data\metadata_IC_APP\metadata_001.mdf',
move 'datasets_001' to 'D:\MSSQL$INST1
\Data\metadata_IC_APP\datasets_001.ndf',
move 'indexes_001' to 'D:\MSSQL$INST1
\Data\metadata_IC_APP\indexes_001.ndf',
move 'logs_001' to 'E:\MSSQL$INST1\log\logs_001.ldf',
recovery,
stats = 5
>--Original Message--
>GM
>How did you perform restore command ? by T-SQL or EM.
>"GM" <gamaglia@.lnc.com> wrote in message
>news:9cf201c35c29$0ee50350$a001280a@.phx.gbl...
>> I was testing a restore using a backup of db1 and
>> restoring to db2. Both db1 and db2 already exist and
have
>> the same file structure. The restore was also moving the
>> db1 files to the db2 file locations. I did not specify
>> with replace. I expected the restore to fail, but it
>> didn't. What am I missing?
>
>.
>
Showing posts with label exist. Show all posts
Showing posts with label exist. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Restore through Job or DTS
I try to schedule either a job or a DTS package that will restore a db from
backup. No other connection exist on that db but apparently I receive the
message that Restore failed because exclusive access to the db could not be
obtained. I assume that the connection that is counted is the connection of
the job itself.
Can anyone help ?Hi ,
Your message confirms that there is some existing connection in the database
when you are try to restore. So please add the below script along with your
DTS package.
use master
go
declare @.x varchar(255)
select @.x = @.x + " kill " + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('dbname')
exec (@.x)
go
Please replace the dbname with your database name in the script.
Thanks
Hari - MCDBA
US Software
Trivandrum
"Andreas" <andreasy@.netu.com.cy> wrote in message
news:uZ7hY6aVDHA.2224@.TK2MSFTNGP09.phx.gbl...
> I try to schedule either a job or a DTS package that will restore a db
from
> backup. No other connection exist on that db but apparently I receive the
> message that Restore failed because exclusive access to the db could not
be
> obtained. I assume that the connection that is counted is the connection
of
> the job itself.
> Can anyone help ?
>
backup. No other connection exist on that db but apparently I receive the
message that Restore failed because exclusive access to the db could not be
obtained. I assume that the connection that is counted is the connection of
the job itself.
Can anyone help ?Hi ,
Your message confirms that there is some existing connection in the database
when you are try to restore. So please add the below script along with your
DTS package.
use master
go
declare @.x varchar(255)
select @.x = @.x + " kill " + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('dbname')
exec (@.x)
go
Please replace the dbname with your database name in the script.
Thanks
Hari - MCDBA
US Software
Trivandrum
"Andreas" <andreasy@.netu.com.cy> wrote in message
news:uZ7hY6aVDHA.2224@.TK2MSFTNGP09.phx.gbl...
> I try to schedule either a job or a DTS package that will restore a db
from
> backup. No other connection exist on that db but apparently I receive the
> message that Restore failed because exclusive access to the db could not
be
> obtained. I assume that the connection that is counted is the connection
of
> the job itself.
> Can anyone help ?
>
Monday, March 12, 2012
Restore prod to dev with different users
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
Restore problem
Since i need to restore my database to another machine, I am now encountering a problem with my db.
Because some login does not exist on the new machine and I need to re-crerate the login in it. However, i found an error message 'User ?? already exist'
Is it possible that i could drop my existing user but some of them are objects/db owner.
many thanks!You got the same problem as me.. see http://dbforums.com/t547764.html|||Originally posted by Nigorr
You got the same problem as me.. see http://dbforums.com/t547764.html
I just find a solution and it fixed my situation and hope it could help you.
I used the sp_change_users_login to associate the login and user.|||thanks, why didn't they(MS) provide a way to do this in enterprise manager?
Because some login does not exist on the new machine and I need to re-crerate the login in it. However, i found an error message 'User ?? already exist'
Is it possible that i could drop my existing user but some of them are objects/db owner.
many thanks!You got the same problem as me.. see http://dbforums.com/t547764.html|||Originally posted by Nigorr
You got the same problem as me.. see http://dbforums.com/t547764.html
I just find a solution and it fixed my situation and hope it could help you.
I used the sp_change_users_login to associate the login and user.|||thanks, why didn't they(MS) provide a way to do this in enterprise manager?
Wednesday, March 7, 2012
Restore of DB
We had a DB A. The person reinstalled SQL, and A now isn't recognized. They never did a backup. The MDF and LDF Files still exist. Is there a way to recreate the DB, and have it look at the old LDF and MDF files, or is there a way to recreate the DB, and copy the information from the old Files over
I think I saw a similar question to this before, but I don't know if it will apply to me. As it referenced to DBs that exist, and mine is about one that used to exist, but somehow went away
DaniCheck out sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dani" <anonymous@.discussions.microsoft.com> wrote in message
news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
> We had a DB A. The person reinstalled SQL, and A now isn't recognized.
They never did a backup. The MDF and LDF Files still exist. Is there a way
to recreate the DB, and have it look at the old LDF and MDF files, or is
there a way to recreate the DB, and copy the information from the old Files
over?
> I think I saw a similar question to this before, but I don't know if it
will apply to me. As it referenced to DBs that exist, and mine is about one
that used to exist, but somehow went away.
> Dani|||Hi Tibor,
I think the .ldf and .mdf files would be on a different
drive ( for example on drive D )when SQL Server was up and
running, and as usual the guy would have formatted Drive
C coz of some issues and Installed the SQL Server again.
Does sp_attach_db suceed, as the database is not detached?
Regards
Thirumal
>--Original Message--
>Check out sp_attach_db.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Dani" <anonymous@.discussions.microsoft.com> wrote in
message
>news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
>> We had a DB A. The person reinstalled SQL, and A now
isn't recognized.
>They never did a backup. The MDF and LDF Files still
exist. Is there a way
>to recreate the DB, and have it look at the old LDF and
MDF files, or is
>there a way to recreate the DB, and copy the information
from the old Files
>over?
>> I think I saw a similar question to this before, but I
don't know if it
>will apply to me. As it referenced to DBs that exist,
and mine is about one
>that used to exist, but somehow went away.
>> Dani
>
>.
>|||> I think the .ldf and .mdf files would be on a different
> drive ( for example on drive D )when SQL Server was up and
> running, and as usual the guy would have formatted Drive
> C coz of some issues and Installed the SQL Server again.
> Does sp_attach_db suceed, as the database is not detached?
It should work, as long as you've got the mdf file you can use the
sp_attach_db, just specify the new file location.
Steve|||I thought sp_detach_db took some data out of master and attached it to the
mdb. I didn't think attach would work without detach.
Christian Smith
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > I think the .ldf and .mdf files would be on a different
> > drive ( for example on drive D )when SQL Server was up and
> > running, and as usual the guy would have formatted Drive
> > C coz of some issues and Installed the SQL Server again.
> > Does sp_attach_db suceed, as the database is not detached?
> It should work, as long as you've got the mdf file you can use the
> sp_attach_db, just specify the new file location.
> Steve
>|||Hi All in my experience especially in this case I have definitely used
sp_attach_single_file_db and passing only the mdf file location, the ldf
file will be auto created and you should be fine. committed transacions that
were not physically written to disk(in controller cache) will be lost
obviously depends on yor cache settings
--
Olu Adedeji
"Christian Smith" <csmith@.digex.com> wrote in message
news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> I thought sp_detach_db took some data out of master and attached it to the
> mdb. I didn't think attach would work without detach.
> Christian Smith
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > > I think the .ldf and .mdf files would be on a different
> > > drive ( for example on drive D )when SQL Server was up and
> > > running, and as usual the guy would have formatted Drive
> > > C coz of some issues and Installed the SQL Server again.
> > > Does sp_attach_db suceed, as the database is not detached?
> >
> > It should work, as long as you've got the mdf file you can use the
> > sp_attach_db, just specify the new file location.
> >
> > Steve
> >
> >
>|||Hi,
This is interesting as I use to think that only cleanly
detached databases can be attached.
Thanks for all your views
Regards
Thirumal
>--Original Message--
>Hi All in my experience especially in this case I have
definitely used
>sp_attach_single_file_db and passing only the mdf file
location, the ldf
>file will be auto created and you should be fine.
committed transacions that
>were not physically written to disk(in controller cache)
will be lost
>obviously depends on yor cache settings
>--
>Olu Adedeji
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
>> I thought sp_detach_db took some data out of master and
attached it to the
>> mdb. I didn't think attach would work without detach.
>> Christian Smith
>> "Steve Thompson" <SteveThompson@.nomail.please> wrote in
message
>> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
>> > > I think the .ldf and .mdf files would be on a
different
>> > > drive ( for example on drive D )when SQL Server was
up and
>> > > running, and as usual the guy would have formatted
Drive
>> > > C coz of some issues and Installed the SQL Server
again.
>> > > Does sp_attach_db suceed, as the database is not
detached?
>> >
>> > It should work, as long as you've got the mdf file
you can use the
>> > sp_attach_db, just specify the new file location.
>> >
>> > Steve
>> >
>> >
>>
>
>.
>|||Sometimes it work to attach a database that wasn't detach, sometimes it
doesn't. We have tons of messages here from users who assumes that attach
would work without a prior detach. Books Online states that attach work *if*
you detached first, so consider the rest a lucky bonus. And for re-creating
the log file, it will only work if the db had only one db and one log file
and it didn't have any recovery work to do (in most cases, it was indeed
properly detached first).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:1339901c3f75f$0b630b60$a001280a@.phx.gbl...
> Hi,
> This is interesting as I use to think that only cleanly
> detached databases can be attached.
> Thanks for all your views
> Regards
> Thirumal
> >--Original Message--
> >Hi All in my experience especially in this case I have
> definitely used
> >sp_attach_single_file_db and passing only the mdf file
> location, the ldf
> >file will be auto created and you should be fine.
> committed transacions that
> >were not physically written to disk(in controller cache)
> will be lost
> >obviously depends on yor cache settings
> >
> >--
> >Olu Adedeji
> >"Christian Smith" <csmith@.digex.com> wrote in message
> >news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> >> I thought sp_detach_db took some data out of master and
> attached it to the
> >> mdb. I didn't think attach would work without detach.
> >>
> >> Christian Smith
> >>
> >> "Steve Thompson" <SteveThompson@.nomail.please> wrote in
> message
> >> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> >> > > I think the .ldf and .mdf files would be on a
> different
> >> > > drive ( for example on drive D )when SQL Server was
> up and
> >> > > running, and as usual the guy would have formatted
> Drive
> >> > > C coz of some issues and Installed the SQL Server
> again.
> >> > > Does sp_attach_db suceed, as the database is not
> detached?
> >> >
> >> > It should work, as long as you've got the mdf file
> you can use the
> >> > sp_attach_db, just specify the new file location.
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >
I think I saw a similar question to this before, but I don't know if it will apply to me. As it referenced to DBs that exist, and mine is about one that used to exist, but somehow went away
DaniCheck out sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dani" <anonymous@.discussions.microsoft.com> wrote in message
news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
> We had a DB A. The person reinstalled SQL, and A now isn't recognized.
They never did a backup. The MDF and LDF Files still exist. Is there a way
to recreate the DB, and have it look at the old LDF and MDF files, or is
there a way to recreate the DB, and copy the information from the old Files
over?
> I think I saw a similar question to this before, but I don't know if it
will apply to me. As it referenced to DBs that exist, and mine is about one
that used to exist, but somehow went away.
> Dani|||Hi Tibor,
I think the .ldf and .mdf files would be on a different
drive ( for example on drive D )when SQL Server was up and
running, and as usual the guy would have formatted Drive
C coz of some issues and Installed the SQL Server again.
Does sp_attach_db suceed, as the database is not detached?
Regards
Thirumal
>--Original Message--
>Check out sp_attach_db.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Dani" <anonymous@.discussions.microsoft.com> wrote in
message
>news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
>> We had a DB A. The person reinstalled SQL, and A now
isn't recognized.
>They never did a backup. The MDF and LDF Files still
exist. Is there a way
>to recreate the DB, and have it look at the old LDF and
MDF files, or is
>there a way to recreate the DB, and copy the information
from the old Files
>over?
>> I think I saw a similar question to this before, but I
don't know if it
>will apply to me. As it referenced to DBs that exist,
and mine is about one
>that used to exist, but somehow went away.
>> Dani
>
>.
>|||> I think the .ldf and .mdf files would be on a different
> drive ( for example on drive D )when SQL Server was up and
> running, and as usual the guy would have formatted Drive
> C coz of some issues and Installed the SQL Server again.
> Does sp_attach_db suceed, as the database is not detached?
It should work, as long as you've got the mdf file you can use the
sp_attach_db, just specify the new file location.
Steve|||I thought sp_detach_db took some data out of master and attached it to the
mdb. I didn't think attach would work without detach.
Christian Smith
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > I think the .ldf and .mdf files would be on a different
> > drive ( for example on drive D )when SQL Server was up and
> > running, and as usual the guy would have formatted Drive
> > C coz of some issues and Installed the SQL Server again.
> > Does sp_attach_db suceed, as the database is not detached?
> It should work, as long as you've got the mdf file you can use the
> sp_attach_db, just specify the new file location.
> Steve
>|||Hi All in my experience especially in this case I have definitely used
sp_attach_single_file_db and passing only the mdf file location, the ldf
file will be auto created and you should be fine. committed transacions that
were not physically written to disk(in controller cache) will be lost
obviously depends on yor cache settings
--
Olu Adedeji
"Christian Smith" <csmith@.digex.com> wrote in message
news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> I thought sp_detach_db took some data out of master and attached it to the
> mdb. I didn't think attach would work without detach.
> Christian Smith
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > > I think the .ldf and .mdf files would be on a different
> > > drive ( for example on drive D )when SQL Server was up and
> > > running, and as usual the guy would have formatted Drive
> > > C coz of some issues and Installed the SQL Server again.
> > > Does sp_attach_db suceed, as the database is not detached?
> >
> > It should work, as long as you've got the mdf file you can use the
> > sp_attach_db, just specify the new file location.
> >
> > Steve
> >
> >
>|||Hi,
This is interesting as I use to think that only cleanly
detached databases can be attached.
Thanks for all your views
Regards
Thirumal
>--Original Message--
>Hi All in my experience especially in this case I have
definitely used
>sp_attach_single_file_db and passing only the mdf file
location, the ldf
>file will be auto created and you should be fine.
committed transacions that
>were not physically written to disk(in controller cache)
will be lost
>obviously depends on yor cache settings
>--
>Olu Adedeji
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
>> I thought sp_detach_db took some data out of master and
attached it to the
>> mdb. I didn't think attach would work without detach.
>> Christian Smith
>> "Steve Thompson" <SteveThompson@.nomail.please> wrote in
message
>> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
>> > > I think the .ldf and .mdf files would be on a
different
>> > > drive ( for example on drive D )when SQL Server was
up and
>> > > running, and as usual the guy would have formatted
Drive
>> > > C coz of some issues and Installed the SQL Server
again.
>> > > Does sp_attach_db suceed, as the database is not
detached?
>> >
>> > It should work, as long as you've got the mdf file
you can use the
>> > sp_attach_db, just specify the new file location.
>> >
>> > Steve
>> >
>> >
>>
>
>.
>|||Sometimes it work to attach a database that wasn't detach, sometimes it
doesn't. We have tons of messages here from users who assumes that attach
would work without a prior detach. Books Online states that attach work *if*
you detached first, so consider the rest a lucky bonus. And for re-creating
the log file, it will only work if the db had only one db and one log file
and it didn't have any recovery work to do (in most cases, it was indeed
properly detached first).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:1339901c3f75f$0b630b60$a001280a@.phx.gbl...
> Hi,
> This is interesting as I use to think that only cleanly
> detached databases can be attached.
> Thanks for all your views
> Regards
> Thirumal
> >--Original Message--
> >Hi All in my experience especially in this case I have
> definitely used
> >sp_attach_single_file_db and passing only the mdf file
> location, the ldf
> >file will be auto created and you should be fine.
> committed transacions that
> >were not physically written to disk(in controller cache)
> will be lost
> >obviously depends on yor cache settings
> >
> >--
> >Olu Adedeji
> >"Christian Smith" <csmith@.digex.com> wrote in message
> >news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> >> I thought sp_detach_db took some data out of master and
> attached it to the
> >> mdb. I didn't think attach would work without detach.
> >>
> >> Christian Smith
> >>
> >> "Steve Thompson" <SteveThompson@.nomail.please> wrote in
> message
> >> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> >> > > I think the .ldf and .mdf files would be on a
> different
> >> > > drive ( for example on drive D )when SQL Server was
> up and
> >> > > running, and as usual the guy would have formatted
> Drive
> >> > > C coz of some issues and Installed the SQL Server
> again.
> >> > > Does sp_attach_db suceed, as the database is not
> detached?
> >> >
> >> > It should work, as long as you've got the mdf file
> you can use the
> >> > sp_attach_db, just specify the new file location.
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >
Restore of DB
We had a DB A. The person reinstalled SQL, and A now isn't recognized. The
y never did a backup. The MDF and LDF Files still exist. Is there a way to
recreate the DB, and have it look at the old LDF and MDF files, or is there
a way to recreate the DB,
and copy the information from the old Files over?
I think I saw a similar question to this before, but I don't know if it will
apply to me. As it referenced to DBs that exist, and mine is about one tha
t used to exist, but somehow went away.
DaniCheck out sp_attach_db.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dani" <anonymous@.discussions.microsoft.com> wrote in message
news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
> We had a DB A. The person reinstalled SQL, and A now isn't recognized.
They never did a backup. The MDF and LDF Files still exist. Is there a way
to recreate the DB, and have it look at the old LDF and MDF files, or is
there a way to recreate the DB, and copy the information from the old Files
over?
> I think I saw a similar question to this before, but I don't know if it
will apply to me. As it referenced to DBs that exist, and mine is about one
that used to exist, but somehow went away.
> Dani|||Hi Tibor,
I think the .ldf and .mdf files would be on a different
drive ( for example on drive D )when SQL Server was up and
running, and as usual the guy would have formatted Drive
C coz of some issues and Installed the SQL Server again.
Does sp_attach_db suceed, as the database is not detached?
Regards
Thirumal
>--Original Message--
>Check out sp_attach_db.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Dani" <anonymous@.discussions.microsoft.com> wrote in
message
>news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
isn't recognized.
>They never did a backup. The MDF and LDF Files still
exist. Is there a way
>to recreate the DB, and have it look at the old LDF and
MDF files, or is
>there a way to recreate the DB, and copy the information
from the old Files
>over?
don't know if it
>will apply to me. As it referenced to DBs that exist,
and mine is about one
>that used to exist, but somehow went away.
>
>.
>|||> I think the .ldf and .mdf files would be on a different
> drive ( for example on drive D )when SQL Server was up and
> running, and as usual the guy would have formatted Drive
> C coz of some issues and Installed the SQL Server again.
> Does sp_attach_db suceed, as the database is not detached?
It should work, as long as you've got the mdf file you can use the
sp_attach_db, just specify the new file location.
Steve|||I thought sp_detach_db took some data out of master and attached it to the
mdb. I didn't think attach would work without detach.
Christian Smith
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> It should work, as long as you've got the mdf file you can use the
> sp_attach_db, just specify the new file location.
> Steve
>|||Hi All in my experience especially in this case I have definitely used
sp_attach_single_file_db and passing only the mdf file location, the ldf
file will be auto created and you should be fine. committed transacions that
were not physically written to disk(in controller cache) will be lost
obviously depends on yor cache settings
Olu Adedeji
"Christian Smith" <csmith@.digex.com> wrote in message
news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> I thought sp_detach_db took some data out of master and attached it to the
> mdb. I didn't think attach would work without detach.
> Christian Smith
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
This is interesting as I use to think that only cleanly
detached databases can be attached.
Thanks for all your views
Regards
Thirumal
>--Original Message--
>Hi All in my experience especially in this case I have
definitely used
>sp_attach_single_file_db and passing only the mdf file
location, the ldf
>file will be auto created and you should be fine.
committed transacions that
>were not physically written to disk(in controller cache)
will be lost
>obviously depends on yor cache settings
>--
>Olu Adedeji
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
attached it to the
message
different
up and
Drive
again.
detached?
you can use the
>
>.
>|||Sometimes it work to attach a database that wasn't detach, sometimes it
doesn't. We have tons of messages here from users who assumes that attach
would work without a prior detach. Books Online states that attach work *if*
you detached first, so consider the rest a lucky bonus. And for re-creating
the log file, it will only work if the db had only one db and one log file
and it didn't have any recovery work to do (in most cases, it was indeed
properly detached first).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:1339901c3f75f$0b630b60$a001280a@.phx
.gbl...
> Hi,
> This is interesting as I use to think that only cleanly
> detached databases can be attached.
> Thanks for all your views
> Regards
> Thirumal
> definitely used
> location, the ldf
> committed transacions that
> will be lost
> attached it to the
> message
> different
> up and
> Drive
> again.
> detached?
> you can use the
y never did a backup. The MDF and LDF Files still exist. Is there a way to
recreate the DB, and have it look at the old LDF and MDF files, or is there
a way to recreate the DB,
and copy the information from the old Files over?
I think I saw a similar question to this before, but I don't know if it will
apply to me. As it referenced to DBs that exist, and mine is about one tha
t used to exist, but somehow went away.
DaniCheck out sp_attach_db.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dani" <anonymous@.discussions.microsoft.com> wrote in message
news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
> We had a DB A. The person reinstalled SQL, and A now isn't recognized.
They never did a backup. The MDF and LDF Files still exist. Is there a way
to recreate the DB, and have it look at the old LDF and MDF files, or is
there a way to recreate the DB, and copy the information from the old Files
over?
> I think I saw a similar question to this before, but I don't know if it
will apply to me. As it referenced to DBs that exist, and mine is about one
that used to exist, but somehow went away.
> Dani|||Hi Tibor,
I think the .ldf and .mdf files would be on a different
drive ( for example on drive D )when SQL Server was up and
running, and as usual the guy would have formatted Drive
C coz of some issues and Installed the SQL Server again.
Does sp_attach_db suceed, as the database is not detached?
Regards
Thirumal
>--Original Message--
>Check out sp_attach_db.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Dani" <anonymous@.discussions.microsoft.com> wrote in
message
>news:297C504B-F507-45CE-82EA-BE2ADC5EF5AF@.microsoft.com...
isn't recognized.
>They never did a backup. The MDF and LDF Files still
exist. Is there a way
>to recreate the DB, and have it look at the old LDF and
MDF files, or is
>there a way to recreate the DB, and copy the information
from the old Files
>over?
don't know if it
>will apply to me. As it referenced to DBs that exist,
and mine is about one
>that used to exist, but somehow went away.
>
>.
>|||> I think the .ldf and .mdf files would be on a different
> drive ( for example on drive D )when SQL Server was up and
> running, and as usual the guy would have formatted Drive
> C coz of some issues and Installed the SQL Server again.
> Does sp_attach_db suceed, as the database is not detached?
It should work, as long as you've got the mdf file you can use the
sp_attach_db, just specify the new file location.
Steve|||I thought sp_detach_db took some data out of master and attached it to the
mdb. I didn't think attach would work without detach.
Christian Smith
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
> It should work, as long as you've got the mdf file you can use the
> sp_attach_db, just specify the new file location.
> Steve
>|||Hi All in my experience especially in this case I have definitely used
sp_attach_single_file_db and passing only the mdf file location, the ldf
file will be auto created and you should be fine. committed transacions that
were not physically written to disk(in controller cache) will be lost
obviously depends on yor cache settings
Olu Adedeji
"Christian Smith" <csmith@.digex.com> wrote in message
news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
> I thought sp_detach_db took some data out of master and attached it to the
> mdb. I didn't think attach would work without detach.
> Christian Smith
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:uc%23X2Qx9DHA.1816@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
This is interesting as I use to think that only cleanly
detached databases can be attached.
Thanks for all your views
Regards
Thirumal
>--Original Message--
>Hi All in my experience especially in this case I have
definitely used
>sp_attach_single_file_db and passing only the mdf file
location, the ldf
>file will be auto created and you should be fine.
committed transacions that
>were not physically written to disk(in controller cache)
will be lost
>obviously depends on yor cache settings
>--
>Olu Adedeji
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:OTMIEwx9DHA.4020@.TK2MSFTNGP09.phx.gbl...
attached it to the
message
different
up and
Drive
again.
detached?
you can use the
>
>.
>|||Sometimes it work to attach a database that wasn't detach, sometimes it
doesn't. We have tons of messages here from users who assumes that attach
would work without a prior detach. Books Online states that attach work *if*
you detached first, so consider the rest a lucky bonus. And for re-creating
the log file, it will only work if the db had only one db and one log file
and it didn't have any recovery work to do (in most cases, it was indeed
properly detached first).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:1339901c3f75f$0b630b60$a001280a@.phx
.gbl...
> Hi,
> This is interesting as I use to think that only cleanly
> detached databases can be attached.
> Thanks for all your views
> Regards
> Thirumal
> definitely used
> location, the ldf
> committed transacions that
> will be lost
> attached it to the
> message
> different
> up and
> Drive
> again.
> detached?
> you can use the
Subscribe to:
Posts (Atom)