473,835 Members | 1,941 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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..sysdevi ces - 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_suspec t_db will not work in a partially
restored database
Jul 20 '05 #1
6 9620
> * 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:\logfil es\myDatabaseLo gBackup1.bak'
WITH NORECOVERY

RESTORE LOG myDatabase
FROM DISK='c:\logfil es\myDatabaseLo gBackup2.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*****@custom ercare.com.au> wrote in message
news:d7******** *************** ***@posting.goo gle.com... 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..sysdevi ces - 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_suspec t_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\my log.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='Recove rLog',
FILENAME='C:\Re coverLog.mdf')
LOG ON(NAME='Recove rLog_Log',
FILENAME='C:\Re coverLog_Log.ld f')
GO
ALTER DATABASE RecoverLog
SET RECOVERY FULL
GO
EXEC sp_dboption 'RecoverLog', 'autoclose', true
GO

-- 2) delete data and log files
EXEC master..xp_cmds hell 'del C:\RecoverLog.m df'
EXEC master..xp_cmds hell 'del C:\RecoverLog_L og.ldf'
GO

-- 3) copy your log file to C:\RecoverLog_L og.ldf'
EXEC master..xp_cmds hell 'copy c:\logfiles\myl og.ldf
C:\RecoverLog_L og.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:\Backup s\RecoverLog.ba k'
WITH NO_TRUNCATE, INIT
GO

DROP DATABASE RecoverLog
GO

-- 5) delete the log file
EXEC master..xp_cmds hell 'del C:\RecoverLog_L og.ldf'
GO

-- 6) restore original database with NORECOVERY
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backup s\MyDatabase.ba k'
WITH NORECOVERY
GO

-- 7) restore log with MOVE and RECOVERY
RESTORE LOG MyDatabase
FROM DISK='C:\Backup s\RecoverLog.ba k'
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*****@custom ercare.com.au> wrote in message
news:d7******** *************** ***@posting.goo gle.com...
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\my log.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*****@custom ercare.com.au> wrote in message
news:d7******** *************** ***@posting.goo gle.com...
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\my log.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*****@custom ercare.com.au> wrote in message
news:d7******** *************** ***@posting.goo gle.com...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1099
by: Vincento Harris | last post by:
Sqlserver 2000 windows 2003 server Database is backed up and restored on a new server with all transaction logs present . Need help with a script to restore database to a a point in time on new server to 2 hours behind current time Your ideas are appreciated as usual
1
1223
by: M&M | last post by:
Dear All, Do anyone know where I can find some useful documentation or any documentation about restoring database to the point in time (using logs). SQL Server 2000 on Win 2000 Thanks for all, M&M
2
3132
by: p175 | last post by:
Hi folks, I've tried reading just about every post I can on raid stiping / extent size etc and I'm just getting myself more confused than ever. Here's my situation. On a Windows Server 2000 standard edition system with only 1gb ram using UDB 8.2.3 Enterprise, I have an existing database that uses two raid arrays striped with 8kb. Presently the tablespaces have a default extent of 24, default prefetch of 48 .
1
2377
by: Robbert van Geldrop | last post by:
Hello, I have a problem restoring Exchange 2000 files. Our software has an interface to ESEBCLI2.dll for online backup and restore features. Everything works fine with Exchange 2003 and also when backing up Exchange 2000, yet restoring Exchange 2000 gives some problems: A Store is restored: * the restore is started.
0
1174
by: DUG | last post by:
There is a server in a remote location. We receive a full .bak of this database to restore on our server, where we use the data for a seperate purpose. The .BAK file is almost 3GB - zipped! They also send us transaction logs 2x / day. We would like to use those, and not download the full .BAK every night. Is it possible (how?) to "update" our version of the live database by just applying the transaction logs, and not have to begin with...
5
6569
by: Troels Arvin | last post by:
Hello, Every so often, I'm asked to help people recover data from tables that were either dropped or where to much data was DELETEed. The complications related to restoring data are a problem. The SAS users are laughing because they can (to a certain extend) easily go back to an earlier SAS table by simply starting dsm. Of course, a flat file table is different than a relational table; but still, the trouble related to restoring DB2...
1
5563
by: GM | last post by:
We have received a backup file that is 6G in size. The log file is about 74G while the MDF is about 5 G when we restored. Is there a way to run a SQL script to restore the .BAK file without restoring the log file as we do not have enough space on the server. PS. We are running Sql2K5. Thanks!
2
8276
by: l0b0 | last post by:
I'm working on a restore procedure for the case where all MDF files are missing, but the LDF files are all intact. A full backup is done every 24 hours, and a log backup is done every 3 hours. After restoring the last full + log backups, is it at all possible to use the LDF files to recover data from that point up to a newer point in time? I've found a post which explains how to do this on SQL Server 2000...
2
1661
by: KeithWalton | last post by:
I know this has been discussed before, but my database is getting huge. Every night I restore backups of my databases to a report server. I don't need the logs, so after restore I detach the database and delete the logs. When I reattach, SQL Server creates new log files that are tiny. If I can do this, why do I need the log files in the first place? As it is, I have to stagger my database restores because I don't have room for the 400 GB of...
0
9803
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10808
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10560
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7766
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6963
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5636
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
3993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3088
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.