restore master database: ldf

Showing posts with label ldf. Show all posts

Friday, March 30, 2012

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!

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

Restore of DB

Restore of database via Veritas 9.1 fails with communication error

We are running SQL 2000 sp4, only one user database (SAP) the database is 63 GB.
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.

Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.

My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.

Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.

When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.

Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.

Just in case anyone else hits this issue:

The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.

Follow on question:

THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.

without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.

It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.

Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too

Saturday, February 25, 2012

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

Friday, March 30, 2012

Restore/Create new db using existing mdf and ldf

Wednesday, March 7, 2012

Restore of DB

Restore of DB

Restore of database via Veritas 9.1 fails with communication error

Saturday, February 25, 2012

Restore MDF file

Restore mdf file

Restore mdf file

Restore mdf file

restore master database

Blog Archive

About Me