Restoring logs to a database

[Edwinah63]

Hi Gurus,

i am having problems with restoring a ms sql database.

i have restored the database using veritas to a different location
('g:\datafiles') in no recover mode.

when i view the database through the Enterprise Manager, it shows the
database as silver icon (loading).

i go to sql analyzer, and put in the restore command

restore log myDatabase
from 'mylog'
with recovery

this produces an error

RESTORE FILELISTONLY
FROM jobs

Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'mylog'. Update sysdevices
and rerun
statement.

i look in master..sysdevices - no entry for mylog, but then no entries
for the log files for any of the other perfectly working databases
either.

i do have a copy of the log files ('c:\logfiles') in another location

i would like the following help if possible:

* a way to update sysdevices with the log file i wish to apply to my
restored database so it will let my restore go through properly.

* a way to specify to use apply the logs in 'c:\logfiles' without
having to give the restore statement a logical name for the log files
(which naturally won't be in sysdevices!)

pls supply transact sql

my thanks

Edwina63

(if wish to email please remove h from edwinah@)

p.s sp_add_log_file_recover_suspect_db will not work in a partially
restored database

[Dan Guzman]

> * a way to specify to use apply the logs in 'c:\logfiles' without

having to give the restore statement a logical name for the log files
(which naturally won't be in sysdevices!)

pls supply transact sql
You can specify the DISK parameter. For example.

RESTORE LOG myDatabase
FROM DISK='c:\logfiles\myDatabaseLogBackup1.bak'
WITH NORECOVERY

RESTORE LOG myDatabase
FROM DISK='c:\logfiles\myDatabaseLogBackup2.bak'
WITH RECOVERY

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Edwinah63" <ed*****@customercare.com.au> wrote in message
news:d7**************************@posting.google.c om... Hi Gurus,

i am having problems with restoring a ms sql database.

i have restored the database using veritas to a different location
('g:\datafiles') in no recover mode.

when i view the database through the Enterprise Manager, it shows the
database as silver icon (loading).

i go to sql analyzer, and put in the restore command

restore log myDatabase
from 'mylog'
with recovery

this produces an error

RESTORE FILELISTONLY
FROM jobs

Server: Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'mylog'. Update sysdevices
and rerun
statement.

i look in master..sysdevices - no entry for mylog, but then no entries
for the log files for any of the other perfectly working databases
either.

i do have a copy of the log files ('c:\logfiles') in another location

i would like the following help if possible:

* a way to update sysdevices with the log file i wish to apply to my
restored database so it will let my restore go through properly.

* a way to specify to use apply the logs in 'c:\logfiles' without
having to give the restore statement a logical name for the log files
(which naturally won't be in sysdevices!)

pls supply transact sql

my thanks

Edwina63

(if wish to email please remove h from edwinah@)

p.s sp_add_log_file_recover_suspect_db will not work in a partially
restored database

[Edwinah63]

Hi Dan,

thanks for your reply.

my question was probably not clear enough re applying a spcified file.

we want to do a point in time recovery after a massive crash and have
no backup of the logs, just copies of the current log in

'c:\logfiles\mylog.ldf'

what i need is a transact sql statement where i can apply the *.ldf
file (not a *.bak or *.dat file) to tables restored from last night's
backup.

regards

Edwina

[Dan Guzman]

As long as you are using the FULL or BULK_LOGGED recovery model in SQL
2000, you can still backup the log with NO_TRUNCATE even if the database
is marked suspect due to inaccessible data files. This log backup can
be applied to your restored database as long as you didn't perform any
log backups since the database backup.

I'm not sure what steps you've taken so far in your recovery but you may
be able to backup your orphaned log file as outlined in the script
below. It seems you've already done step 6.

/*
1) create a new database with FULL model
2) delete data and log files
3) copy your log file to same name as deleted log file above
4) backup the log with NO_TRUNCATE
5) delete the log file
6) restore original database with NORECOVERY
7) restore log with MOVE and RECOVERY
*/

-- 1) create a new database with FULL model
USE master
GO
CREATE DATABASE RecoverLog
ON(NAME='RecoverLog',
FILENAME='C:\RecoverLog.mdf')
LOG ON(NAME='RecoverLog_Log',
FILENAME='C:\RecoverLog_Log.ldf')
GO
ALTER DATABASE RecoverLog
SET RECOVERY FULL
GO
EXEC sp_dboption 'RecoverLog', 'autoclose', true
GO

-- 2) delete data and log files
EXEC master..xp_cmdshell 'del C:\RecoverLog.mdf'
EXEC master..xp_cmdshell 'del C:\RecoverLog_Log.ldf'
GO

-- 3) copy your log file to C:\RecoverLog_Log.ldf'
EXEC master..xp_cmdshell 'copy c:\logfiles\mylog.ldf
C:\RecoverLog_Log.ldf'
GO

-- 4) backup the log with NO_TRUNCATE
-- Note that backup will error due to inaccessible data file
-- but log will still be backed up.
BACKUP LOG RecoverLog
TO DISK='C:\Backups\RecoverLog.bak'
WITH NO_TRUNCATE, INIT
GO

DROP DATABASE RecoverLog
GO

-- 5) delete the log file
EXEC master..xp_cmdshell 'del C:\RecoverLog_Log.ldf'
GO

-- 6) restore original database with NORECOVERY
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH NORECOVERY
GO

-- 7) restore log with MOVE and RECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backups\RecoverLog.bak'
WITH MOVE 'RecoverLog_Log' TO 'C:\MyDatabase_Log.ldf',
RECOVERY
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Edwinah63" <ed*****@customercare.com.au> wrote in message
news:d7**************************@posting.google.c om...

Hi Dan,

thanks for your reply.

my question was probably not clear enough re applying a spcified file.

we want to do a point in time recovery after a massive crash and have
no backup of the logs, just copies of the current log in

'c:\logfiles\mylog.ldf'

what i need is a transact sql statement where i can apply the *.ldf
file (not a *.bak or *.dat file) to tables restored from last night's
backup.

regards

Edwina

[Greg D. Moore \(Strider\)]

"Edwinah63" <ed*****@customercare.com.au> wrote in message
news:d7**************************@posting.google.c om...

Hi Dan,

thanks for your reply.

my question was probably not clear enough re applying a spcified file.

we want to do a point in time recovery after a massive crash and have
no backup of the logs, just copies of the current log in

'c:\logfiles\mylog.ldf'

what i need is a transact sql statement where i can apply the *.ldf
file (not a *.bak or *.dat file) to tables restored from last night's
backup.
I don't believe this is possible.

You'll probably have to open a case with Microsoft tech support.


regards

Edwina

[Edwinah63]

Hi Dan,

"Hope this helps" ?

it most certainly does!!

thank you for taking the time to post such a comprehensive reply. as
it happens i have spent the day researching a restore methodology
which is fairly similar to the one you have recommended, so it is good
to see my logic corroborated by someone else.

all of this came about because of a huge crash we had yesterday
afternoon and it took us approx 10 hours to fix.

many many thanks

Edwinah63

[Dan Guzman]

Glad it helped. It pays to practice your disaster recovery plan under a
variety of scenarios so that you have confidence in your DR procedures.
Like Greg mentioned, you can always call Microsoft PSS if you find
yourself in a bind.

--
Dan Guzman
SQL Server MVP
"Edwinah63" <ed*****@customercare.com.au> wrote in message
news:d7**************************@posting.google.c om...

Hi Dan,

"Hope this helps" ?

it most certainly does!!

thank you for taking the time to post such a comprehensive reply. as
it happens i have spent the day researching a restore methodology
which is fairly similar to the one you have recommended, so it is good
to see my logic corroborated by someone else.

all of this came about because of a huge crash we had yesterday
afternoon and it took us approx 10 hours to fix.

many many thanks

Edwinah63