Restored Backup problem

I had to reinstall my entire Server week. I had everything of importance
backed up.
When I put my Sql Server database .MDF files back, I found that although the
Users within the databases were there, the Logins were not. Where are the
Logins stored? I have my entire previous hard disk contents available. I jus
t
need to know what to restore.Hi
In master database.
t_4g4w.asp" target="_blank">http://msdn.microsoft.com/library/d... />
t_4g4w.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:87C35929-C18C-4C8A-A80C-B36283866EE2@.microsoft.com...
>I had to reinstall my entire Server week. I had everything of importance
> backed up.
> When I put my Sql Server database .MDF files back, I found that although
> the
> Users within the databases were there, the Logins were not. Where are the
> Logins stored? I have my entire previous hard disk contents available. I
> just
> need to know what to restore.|||Hello Mike,
Yes I should have thought of that. It works fine now. Thank you.
Ian

Restore/Create new db using existing mdf and ldf

Hi All,

I'm sure there's a simple answer for this but I haven't found anything so far that gives me the info I'm needing, so if anyone could help, I'd be grateful!

I've been given mdf and ldf files from an existing off-site database to be "restored" locally (sqlserver 7). Usually I restore from a backup file, so I haven't had any experience with this scenario.

I've found info regarding sp_attach...if I create a new database locally and run sp_attach to point to the mdf and ldf files, will this work?

Many thanks in advance for any suggestions!

Cheers,
Megan:rolleyes:

Have answered my own question and for anyone who might have the same issue, yes it can be done by sp_attach_db very easily.
This will create a new database, and you just need to configure your users (sp_change_users_login), etc, again when it's attached.

Easy as!

Restore SP

How to you restore SP from one db to another? I tried a
backup restore and get the message "you can't over write
the .mdf file?What do you mean by "SP"? Stored procedure? If so, just generate scrip from
the existing database and apply that script on the destination database.
--
Tibor Karaszi
"Shelley" <anonymous@.discussions.microsoft.com> wrote in message
news:0f2101c3a3a8$bea16fb0$a101280a@.phx.gbl...
> How to you restore SP from one db to another? I tried a
> backup restore and get the message "you can't over write
> the .mdf file?

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
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >

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

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

OK

looks like that database is corrupted. You should restore from an earlier/different backup.

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

OK

looks like that database is corrupted. You should restore from an earlier/different backup.

Restore MDF file

Hi all,
I need a urgent help. My server crashed yesterday because of the harddisk
problem. I lost my backup file and only got this *.mdf and *.ldf file.
I tried to restore these using attach database method and also by creating a
database...stop the sql service... overwrite the created database with the m
y
*.mdf and *.ldf file.
First method was showing this message "The File you've specified is not a
valid SQL server database file"
Second method ... Database is in Suspect mode ...If I tried to click on the
database, it will show "Error 823: I/O error (bad page ID) detected during
read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\GTS_MY.mdf"
Pls advise what I have to do to restore the above file...
Million Thanks in advance for any help regarding this...
Regards
SajithHi
Try these methods , I hope it will help you.
create a new database of the same name and log file and location as the old
database and log file
get rid of the old database.
you may be able to right click delete it in this situation or used
sp_removedb
create a new database of the right size and shape with correct log and data
file locations
stop sql
rename the new databases.mdf or delete it if you don't have enough space -
do not touch the .ldf
move back in the old database .mdf file or rename it back again
restart sql server
it should come up suspect
--
1. From a query window, set the status so that you can update the system
tables by running the following query:
use Master
go
sp_configure "allow", 1
go
reconfigure with override
go
2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:
update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go
3. Go into the data directory (MSSQL2000\DATA) and rename the log file
associated
the DB in question (XXXX.ldf) to some
temporary name, such as XXXX.TMP.
4. Exit the query window.
5. Then start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.
6. Bring up another query window and verify that the DB is in emergency mode
by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'
7. Verify that the status is 32768. If it is, then issue the query:
dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
the quotation marks
REBUILD_LOG should take less than 5 minutes even on a very large
database. It should complete with the message
DBCC execution completed
8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = '<DBName>'
9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
restart SQL server. Verify the status of the
database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:94C942B9-D3FA-49BC-8468-147071C844F7@.microsoft.com...
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
> a
> database...stop the sql service... overwrite the created database with the
> my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on
> the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||Please do not multipost, answered in .server.
HTH, Jens Suessmeyer.|||Hi,
First let me thank you for replying to my issue...
I have followed through the steps you have mentioned... I had problem with
7th step. I am pasting the error message from the query analyser
dbcc traceon(3604)
dbcc rebuild_log('GTS_MY','GTS_MY_log')
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 5105, Level 16, State 2, Line 2
Device activation error. The physical file name 'GTS_MY_log' may be incorrec
t.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Pls advise...
Thanks a million...
Regards
Sajith
"Uri Dimant" wrote:

> Hi
> Try these methods , I hope it will help you.
>
> create a new database of the same name and log file and location as the ol
d
> database and log file
> get rid of the old database.
> you may be able to right click delete it in this situation or used
> sp_removedb
> create a new database of the right size and shape with correct log and dat
a
> file locations
> stop sql
> rename the new databases.mdf or delete it if you don't have enough space -
> do not touch the .ldf
> move back in the old database .mdf file or rename it back again
> restart sql server
> it should come up suspect
> --
> 1. From a query window, set the status so that you can update the system
> tables by running the following query:
> use Master
> go
> sp_configure "allow", 1
> go
> reconfigure with override
> go
> 2. Then set the status of the DB that is giving you the problem (XXXXX) in
to
> Emergency Mode by running the following query:
> update sysdatabases set status = 32768 where name = '<DBName>'
> go
> checkpoint
> go
> shutdown with nowait
> go
> 3. Go into the data directory (MSSQL2000\DATA) and rename the log file
> associated
> the DB in question (XXXX.ldf) to some
> temporary name, such as XXXX.TMP.
> 4. Exit the query window.
> 5. Then start up SQL Server from a DOS command window by issuing:
> sqlservr -c -T3608 -T4022.
> 6. Bring up another query window and verify that the DB is in emergency mo
de
> by issuing:
> select Name, Status from Sysdatabases where name = '<DB_Name>'
> 7. Verify that the status is 32768. If it is, then issue the query:
> dbcc traceon(3604)
> dbcc rebuild_log('<DB_Name>','<log_filename>') <-- You will need
> the quotation marks
> REBUILD_LOG should take less than 5 minutes even on a very large
> database. It should complete with the message
> DBCC execution completed
> 8. Take the database out of bypass recovery mode by issuing the command
> update sysdatabases set status = 0 where name = '<DBName>'
> 9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and
> restart SQL server. Verify the status of the
> database by running DBCC NEWALLOC and DBCC CHECKDB on the database.
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:94C942B9-D3FA-49BC-8468-147071C844F7@.microsoft.com...
>
>|||Ok , make sure that the 'old' path of the LOG file is the same as a 'new'
one
How many Logs files do you have?
http://support.microsoft.com/defaul...kb;en-us;271223
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:49501D5E-487F-42AC-9566-E64985793726@.microsoft.com...
> Hi,
> First let me thank you for replying to my issue...
> I have followed through the steps you have mentioned... I had problem
> with
> 7th step. I am pasting the error message from the query analyser
> dbcc traceon(3604)
> dbcc rebuild_log('GTS_MY','GTS_MY_log')
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 5105, Level 16, State 2, Line 2
> Device activation error. The physical file name 'GTS_MY_log' may be
> incorrect.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Pls advise...
> Thanks a million...
> Regards
> Sajith
> "Uri Dimant" wrote:
>|||Hi,
Thanks very much for the reply.
you are right. It is not the same location. Earlier it was in
E:\Pro...mssql\data ... Right now our server has got only two drives. c: an
d
d:... I was trying d drive for this... so log files need to be in this
location. ? Is there any way to change this part for restoration... ?
Thanks again...
Regards
sajith
"Uri Dimant" wrote:

> Ok , make sure that the 'old' path of the LOG file is the same as a 'new'
> one
> How many Logs files do you have?
> http://support.microsoft.com/defaul...kb;en-us;271223
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:49501D5E-487F-42AC-9566-E64985793726@.microsoft.com...
>
>|||Please take a look at WITH MOVE option in the BOL
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:B8095D52-E924-4C56-90F6-7C35453329F7@.microsoft.com...
> Hi,
> Thanks very much for the reply.
> you are right. It is not the same location. Earlier it was in
> E:\Pro...mssql\data ... Right now our server has got only two drives. c:
> and
> d:... I was trying d drive for this... so log files need to be in this
> location. ? Is there any way to change this part for restoration... ?
> Thanks again...
> Regards
> sajith
> "Uri Dimant" wrote:
>|||Hi,
managed to restore the files in the same locations as it was earlier(before
the crash). But I still have the same problem in thelast step(during dbcc
rebuiid_Log). I have only one error log file.
pls advise
Thanks a ton for the help you are providing now...
Regards
Sajith
"Uri Dimant" wrote:

> Please take a look at WITH MOVE option in the BOL
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:B8095D52-E924-4C56-90F6-7C35453329F7@.microsoft.com...
>
>|||Try
EXEC sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.mdf'
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:08B47B26-BAD2-4FAB-823D-33641DFDE9A8@.microsoft.com...
> Hi,
> managed to restore the files in the same locations as it was
> earlier(before
> the crash). But I still have the same problem in thelast step(during dbcc
> rebuiid_Log). I have only one error log file.
> pls advise
> Thanks a ton for the help you are providing now...
> Regards
> Sajith
>
> "Uri Dimant" wrote:
>|||I am getting the error 823
I/O erro(bad page id)....
Right now i am trying to get the data from old files... i thikn it is
corrupted now... its heavy work for me...
Thanks very much for supporting for my issue...even though it is not
solved.. you have spent very long time for this. I really appreciate ...
Thanks again
Best Regards
Sajith
"Uri Dimant" wrote:

> Try
> EXEC sp_attach_single_file_db @.dbname = 'dbname',
> @.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.md
f'
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:08B47B26-BAD2-4FAB-823D-33641DFDE9A8@.microsoft.com...
>
>

Restore mdf file

Hi all,
I need a urgent help. My server crashed yesterday because of the harddisk
problem. I lost my backup file and only got this *.mdf and *.ldf file.
I tried to restore these using attach database method and also by creating a
database...stop the sql service... overwrite the created database with the my
*.mdf and *.ldf file.
First method was showing this message "The File you've specified is not a
valid SQL server database file"
Second method ... Database is in Suspect mode ...If I tried to click on the
database, it will show "Error 823: I/O error (bad page ID) detected during
read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\GTS_MY.mdf"
Pls advise what I have to do to restore the above file...
Million Thanks in advance for any help regarding this...
Regards
Sajith
Take a look at this magic page:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
|||Hi
Most of the articles relating to error 823
(http://search.msdn.microsoft.com/sea...=&qn=&c=10&s=1)
assume that the database is already attached, therefore you may want to raise
a call with PSS to get this resolved. Alternatively if you have a backups
(full/log) you may be able to restore upto a point in time (although there
will almost certainly be some loss of data).
John
"Sajith" wrote:

> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating a
> database...stop the sql service... overwrite the created database with the my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith
|||see this link
http://www.mrdk.nl/FAQ/dbrepair.txt
regards
Michel Posseth [MCP]
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:A612F8D4-2D2C-4FB2-855C-2080619DC938@.microsoft.com...
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
> a
> database...stop the sql service... overwrite the created database with the
> my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on
> the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith
|||hi
let me thank you for replying to my mail.
dbcc rebuild_log('GTS_MY','C:\Program Files\Microsoft Sql
Server\mssql\data\GTS_MY_log.ldf')
above is my command... message also says completed successfuly... But I
cant see the *.ldf file in the folder . database is still in suspect mode..
what could be the problem..
Thanks again...
Regards
Sajith
"m.posseth" wrote:

> see this link
> http://www.mrdk.nl/FAQ/dbrepair.txt
>
>
> regards
> Michel Posseth [MCP]
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:A612F8D4-2D2C-4FB2-855C-2080619DC938@.microsoft.com...
>
>
|||Hi Sajithy,
you are doing the DBCC for the log file where your originally error was
stating the mdf file had the problem.
Try following that line to resolve this issue.
Good Luck and post back your results and resolution
Cheers
Steve L

Restore mdf file

Hi all,
I need a urgent help. My server crashed yesterday because of the harddisk
problem. I lost my backup file and only got this *.mdf and *.ldf file.
I tried to restore these using attach database method and also by creating a
database...stop the sql service... overwrite the created database with the my
*.mdf and *.ldf file.
First method was showing this message "The File you've specified is not a
valid SQL server database file"
Second method ... Database is in Suspect mode ...If I tried to click on the
database, it will show "Error 823: I/O error (bad page ID) detected during
read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\GTS_MY.mdf"
Pls advise what I have to do to restore the above file...
Million Thanks in advance for any help regarding this...
Regards
SajithTake a look at this magic page:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
HTH, Jens Suessmeyer.|||Hi
Most of the articles relating to error 823
(http://search.msdn.microsoft.com/search/results.aspx?view=msdn&st=a&na=81&qu=&qp=bad+page+ID&qa=&qn=&c=10&s=1)
assume that the database is already attached, therefore you may want to raise
a call with PSS to get this resolved. Alternatively if you have a backups
(full/log) you may be able to restore upto a point in time (although there
will almost certainly be some loss of data).
John
"Sajith" wrote:
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating a
> database...stop the sql service... overwrite the created database with the my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||see this link
http://www.mrdk.nl/FAQ/dbrepair.txt
regards
Michel Posseth [MCP]
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:A612F8D4-2D2C-4FB2-855C-2080619DC938@.microsoft.com...
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
> a
> database...stop the sql service... overwrite the created database with the
> my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on
> the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||hi
let me thank you for replying to my mail.
dbcc rebuild_log('GTS_MY','C:\Program Files\Microsoft Sql
Server\mssql\data\GTS_MY_log.ldf')
above is my command... message also says completed successfuly... But I
cant see the *.ldf file in the folder . database is still in suspect mode..
what could be the problem..
Thanks again...
Regards
Sajith
"m.posseth" wrote:
> see this link
> http://www.mrdk.nl/FAQ/dbrepair.txt
>
>
> regards
> Michel Posseth [MCP]
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:A612F8D4-2D2C-4FB2-855C-2080619DC938@.microsoft.com...
> > Hi all,
> >
> > I need a urgent help. My server crashed yesterday because of the harddisk
> > problem. I lost my backup file and only got this *.mdf and *.ldf file.
> >
> > I tried to restore these using attach database method and also by creating
> > a
> > database...stop the sql service... overwrite the created database with the
> > my
> > *.mdf and *.ldf file.
> > First method was showing this message "The File you've specified is not a
> > valid SQL server database file"
> >
> > Second method ... Database is in Suspect mode ...If I tried to click on
> > the
> > database, it will show "Error 823: I/O error (bad page ID) detected during
> > read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> > SQL
> > Server\MSSQL\Data\GTS_MY.mdf"
> >
> > Pls advise what I have to do to restore the above file...
> >
> > Million Thanks in advance for any help regarding this...
> >
> > Regards
> > Sajith
>
>|||Hi Sajithy,
you are doing the DBCC for the log file where your originally error was
stating the mdf file had the problem.
Try following that line to resolve this issue.
Good Luck and post back your results and resolution
Cheers
Steve L

Restore mdf file

Hi all,
I need a urgent help. My server crashed yesterday because of the harddisk
problem. I lost my backup file and only got this *.mdf and *.ldf file.
I tried to restore these using attach database method and also by creating a
database...stop the sql service... overwrite the created database with the m
y
*.mdf and *.ldf file.
First method was showing this message "The File you've specified is not a
valid SQL server database file"
Second method ... Database is in Suspect mode ...If I tried to click on the
database, it will show "Error 823: I/O error (bad page ID) detected during
read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\GTS_MY.mdf"
Pls advise what I have to do to restore the above file...
Million Thanks in advance for any help regarding this...
Regards
SajithTake a look at this magic page:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.|||Hi
Most of the articles relating to error 823
(bad+page+ID&qa=&qn=&c=10&s=1" target="_blank">http://search.msdn.microsoft.com/se...a=&qn=&c=10&s=1)
assume that the database is already attached, therefore you may want to rais
e
a call with PSS to get this resolved. Alternatively if you have a backups
(full/log) you may be able to restore upto a point in time (although there
will almost certainly be some loss of data).
John
"Sajith" wrote:

> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
a
> database...stop the sql service... overwrite the created database with the
my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on t
he
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft SQ
L
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||see this link
http://www.mrdk.nl/FAQ/dbrepair.txt
regards
Michel Posseth [MCP]
"Sajith" <Sajith@.discussions.microsoft.com> wrote in message
news:A612F8D4-2D2C-4FB2-855C-2080619DC938@.microsoft.com...
> Hi all,
> I need a urgent help. My server crashed yesterday because of the harddisk
> problem. I lost my backup file and only got this *.mdf and *.ldf file.
> I tried to restore these using attach database method and also by creating
> a
> database...stop the sql service... overwrite the created database with the
> my
> *.mdf and *.ldf file.
> First method was showing this message "The File you've specified is not a
> valid SQL server database file"
> Second method ... Database is in Suspect mode ...If I tried to click on
> the
> database, it will show "Error 823: I/O error (bad page ID) detected during
> read at offset (0x0000000012000 in the file 'C:\Program Files\Microsoft
> SQL
> Server\MSSQL\Data\GTS_MY.mdf"
> Pls advise what I have to do to restore the above file...
> Million Thanks in advance for any help regarding this...
> Regards
> Sajith|||hi
let me thank you for replying to my mail.
dbcc rebuild_log('GTS_MY','C:\Program Files\Microsoft Sql
Server\mssql\data\GTS_MY_log.ldf')
above is my command... message also says completed successfuly... But I
cant see the *.ldf file in the folder . database is still in suspect mode..
what could be the problem..
Thanks again...
Regards
Sajith
"m.posseth" wrote:

> see this link
> http://www.mrdk.nl/FAQ/dbrepair.txt
>
>
> regards
> Michel Posseth [MCP]
>
>
>
> "Sajith" <Sajith@.discussions.microsoft.com> wrote in message
> news:A612F8D4-2D2C-4FB2-855C-2080619DC938@.microsoft.com...
>
>|||Hi Sajithy,
you are doing the DBCC for the log file where your originally error was
stating the mdf file had the problem.
Try following that line to resolve this issue.
Good Luck and post back your results and resolution
Cheers
Steve L

Restore MDF and NDF to a single DB on a new location

I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
Max
No you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max

Restore MDF and NDF to a single DB on a new location

I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only hav
e
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max

Restore MDF and NDF to a single DB on a new location

I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
--
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max

Restore mdf after system crash in sqls2k

During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?
Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?
|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:

> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
>
>
|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:

> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?
|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.

Restore mdf after system crash in sqls2k

During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
--
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> > During a system crash I was able to manually copy my .mdf but not the log.
> > After restoring the system sql runs an older version of the file, which i
> > want to replace with the copied mdf. Sql manager denies attaching the file
> > because it doesn't mach the log-file.
> > Is there a way of resetting the database or any other way for me to
> > restore
> > the .mdf without dataloss?
>
>|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.