Restoring SQL 7 db's created on NT4-Svr onto a W2K-Svr

Hello,
I am trying to help a small charity whose old NT4 Server
has crashed and has had to be written-off. I have a
Veritas Backup Exec tape with all their SQL Server 7
(SP1) datases on it. The tape was created using the
appropriate Backup Exec SQL Agent. There are no SQL dump
files on it - only the Veritas live database backups.
The new server that the charity has purchased will not
run NT4 Server. It is loaded with W2K Server.
Is it possible to recover their system by performing a
fresh installation of SQL Server 7 onto the new W2K
Server and then restoring all the SQL Server 7 databases
(master , model, msdb + user databases) from the backup
tape into the fresh intallation?
Thanks to anyone who can help.
Paul> Is it possible to recover their system by performing a
> fresh installation of SQL Server 7 onto the new W2K
> Server and then restoring all the SQL Server 7 databases
> (master , model, msdb + user databases) from the backup
> tape into the fresh intallation?
Yes, that should be possible. You probably need the new install having the same service pack for the
system databases. However, I do not know in which way using Veritas might complicate this issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <sheenpaul@.hotmail.com> wrote in message news:044601c39b4d$83daaf80$a301280a@.phx.gbl...
> Hello,
> I am trying to help a small charity whose old NT4 Server
> has crashed and has had to be written-off. I have a
> Veritas Backup Exec tape with all their SQL Server 7
> (SP1) datases on it. The tape was created using the
> appropriate Backup Exec SQL Agent. There are no SQL dump
> files on it - only the Veritas live database backups.
> The new server that the charity has purchased will not
> run NT4 Server. It is loaded with W2K Server.
> Is it possible to recover their system by performing a
> fresh installation of SQL Server 7 onto the new W2K
> Server and then restoring all the SQL Server 7 databases
> (master , model, msdb + user databases) from the backup
> tape into the fresh intallation?
> Thanks to anyone who can help.
> Paul|||Thanks for your encouraging reply, Tibor. Here is an
update in case others get a similar issue.
The restore of the SQL 7 master database from the Veritas
backup tape is proving to be a problem (the restore job
hangs after only 5% with no error messages) but all other
system & user databases restore without a problem. I am
trying to resolve this matter with Veritas support and
will post again later.
>--Original Message--
>> Is it possible to recover their system by performing a
>> fresh installation of SQL Server 7 onto the new W2K
>> Server and then restoring all the SQL Server 7
databases
>> (master , model, msdb + user databases) from the backup
>> tape into the fresh intallation?
>Yes, that should be possible. You probably need the new
install having the same service pack for the
>system databases. However, I do not know in which way
using Veritas might complicate this issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul" <sheenpaul@.hotmail.com> wrote in message
news:044601c39b4d$83daaf80$a301280a@.phx.gbl...
>> Hello,
>> I am trying to help a small charity whose old NT4
Server
>> has crashed and has had to be written-off. I have a
>> Veritas Backup Exec tape with all their SQL Server 7
>> (SP1) datases on it. The tape was created using the
>> appropriate Backup Exec SQL Agent. There are no SQL
dump
>> files on it - only the Veritas live database backups.
>> The new server that the charity has purchased will not
>> run NT4 Server. It is loaded with W2K Server.
>> Is it possible to recover their system by performing a
>> fresh installation of SQL Server 7 onto the new W2K
>> Server and then restoring all the SQL Server 7
databases
>> (master , model, msdb + user databases) from the backup
>> tape into the fresh intallation?
>> Thanks to anyone who can help.
>> Paul
>
>.
>

restoring SQL 2000 on Windows 2000

i am trying to restore sql 2000 on Windows 2000 from a
tape backup created thru Veritas 8.6. The restore
completes successfully and everything seems fine except
for the fact that the sql service fails to start and when
i try to start sql, it returns the error message "the path
was not found"I'm guessing that one of your parameters in the registry point to some
location different then where you restored the files. Review the registry
entries in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLSever\Parameters
Make sure all the parmeters poin to valid files, the ones you restore.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"malejandro" <malejandrdo@.armadillohomes.com> wrote in message
news:12d0401c44349$e884d6f0$a601280a@.phx
.gbl...
> i am trying to restore sql 2000 on Windows 2000 from a
> tape backup created thru Veritas 8.6. The restore
> completes successfully and everything seems fine except
> for the fact that the sql service fails to start and when
> i try to start sql, it returns the error message "the path
> was not found"

restoring SQL 2000 on Windows 2000

i am trying to restore sql 2000 on windows 2000 from a
tape backup created thru Veritas 8.6. The restore
completes successfully and everything seems fine except
for the fact that the sql service fails to start and when
i try to start sql, it returns the error message "the path
was not found"I'm guessing that one of your parameters in the registry point to some
location different then where you restored the files. Review the registry
entries in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLSever\Parameters
Make sure all the parmeters poin to valid files, the ones you restore.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"malejandro" <malejandrdo@.armadillohomes.com> wrote in message
news:12d0401c44349$e884d6f0$a601280a@.phx.gbl...
> i am trying to restore sql 2000 on windows 2000 from a
> tape backup created thru Veritas 8.6. The restore
> completes successfully and everything seems fine except
> for the fact that the sql service fails to start and when
> i try to start sql, it returns the error message "the path
> was not found"

restoring SQL 2000 on Windows 2000

i am trying to restore sql 2000 on windows 2000 from a
tape backup created thru Veritas 8.6. The restore
completes successfully and everything seems fine except
for the fact that the sql service fails to start and when
i try to start sql, it returns the error message "the path
was not found"
I'm guessing that one of your parameters in the registry point to some
location different then where you restored the files. Review the registry
entries in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLSever\P arameters
Make sure all the parmeters poin to valid files, the ones you restore.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"malejandro" <malejandrdo@.armadillohomes.com> wrote in message
news:12d0401c44349$e884d6f0$a601280a@.phx.gbl...
> i am trying to restore sql 2000 on windows 2000 from a
> tape backup created thru Veritas 8.6. The restore
> completes successfully and everything seems fine except
> for the fact that the sql service fails to start and when
> i try to start sql, it returns the error message "the path
> was not found"
sql

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