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> * 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...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------
"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:d714e824.0309080030.393bc6ce@.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|||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|||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...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------
"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:d714e824.0309081520.1805b933@.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|||"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:d714e824.0309081520.1805b933@.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|||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|||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" <edwinah@.customercare.com.au> wrote in message
news:d714e824.0309082139.72a1386e@.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
No comments:
Post a Comment