469,319 Members | 2,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,319 developers. It's quick & easy.

Restoring logs to a database

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
Jul 20 '05 #1
6 9261
> * 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

Jul 20 '05 #2
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
Jul 20 '05 #3
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

Jul 20 '05 #4

"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

Jul 20 '05 #5
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
Jul 20 '05 #6
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

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Vincento Harris | last post: by
1 post views Thread by Robbert van Geldrop | last post: by
5 posts views Thread by Troels Arvin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.