Restore of SQL 2K

antipodes1

Active Member
Hi there,

we are attempting to design a restore procedure for our enterprise server and are running into trouble with the restore of the database (SQL 2K). I would be interested to know the methods others use to restore their SQL database. We have tried a TIVOLI backup of the database and then a restore which is failing.

In the past I would have reinstalled SQL and then overwritten it with a backup, or maybe reinstalled SQL and attached files from a flatfile backup of the sql files. There seems to be lots of information about restoring a single dbase out there, but what about restoring a complete SQL server? Anyone with the answers?

Thanks

Gordon

OneWorld Xe Sp16_018, W2k, SQL 2K on Compaq Proliant.
 
Gordon :

I backup and restore my SQL databases with SQL procedures, I prefer not to
use third party tools.

BACKUP DATABASE xxxx TO TAPE='\\.\TAPE0' WITH INIT (or NOINIT) blabla
RESTORE DATABASE xxxx FROM TAPE='\\.\TAPE0' WITH REPLACE,RECOVERY, blabla

Depending on the kind of disaster, you may or not need to reinstall the full
SQL installation.

Sebastian Sajaroff
 
Remember that SQL Server knows about your databases
and server logins because of records in the master
database tables. Ensure that any full recovery plan
includes restoring the master database in addition to
the user databases. If you do not do this you will
have to reattach your user databases so master knows
of them.


--- antipodes1 <[email protected]> wrote:
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OW&Number=27003


__________________________________________________
 
We're running SQL 7.0 here, and we had tried 3rd party solutions in the past, but now we just back it up to a remote fileserver (transaction logs during the day, full backups at night). It does require a fair amount of disk space, (The roughly the same amount of your database files) and make sure you backup your master, model, and msdb db's as well. There's some fairly good documentation out there on the restoral end (check around for a good SQL 2000 Admin book, its easier in 2k than in 7.0), and from what I've seen it's the fastest and cleanest method out there. Right now our nightly full backups take just over an hour for 18 gigs worth of db space (backup of all db's) and restoral is a snap. Good luck!

- Scott

B7332 sp10.1 SQL7.0 NT 4.0 sp5
 
I should point out that we have not suffered a disaster yet! The guys here are just testing out the scenario.
Their current procedure is to take a full backup of the whole server using TIVOLI with database backup functionality so that we don't have to stop SQL. What they are then doing is applying that backup to another server without any OS or SQL installed and then just starting it up. but it doesn't work!! SQL Server fails to start. Its not the way I would do it, but I need some suggestions to point them in the right direction...so thanks for the ideas so far! At another place I was at we used to do the same as scotto72. Worked great.

Gordon

OneWorld Xe Sp16_018, W2k, SQL 2K on Compaq Proliant.
 
Gordon :

You can't move SQL databases just like that (i.e. as if they were regular
flat files...)
You need to have SQL installed on both sides, backup SQL databases with SQL
backup commands or
a SQL Compliant third party backup tool; and then restore those files on the
other server using
SQL restore commands or the compliant tool.
Your SQL backups should include master, msdb, model and user (JDE*)
databases following any
order; but restore should start from master database.

Sebastian Sajaroff
 
I'm not familiar with Tivoli, are they backing up and restoring Registry values also? Very little windows software will run correctly by just plunking the files down on a new box.

As you said Gordon, their approach is not the way I would do it either (burned too many times).

Cheers,

Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials
 
Scott has mentioned that the backup of the SQL system databases is important
for SQL recovery. He has also mentioned that there is documentation within
SQL on how to restore SQL (I have not had the opportunity to try it as the
need has not yet arrived. We do however have a DR project later this year to
test the recovery process.) We are also using SQL to backup the databases to
file but we will have to discontinue this method in the near future when our
databases get too big. The next option would be to use a third party product
such as Arcserve and Veritas that have backup agent options that can backup
open SQL database files and also allow recovery. However I would strongly
recommend that the use of agents should be tested thoroughly as I have heard
of unreliability using these products (someone on the list may be able to
give their experiences). With NT, it is a similar type of process but my
guess that most shops do the system manual rebuild method and restore the
SQL databases separately rather than trying to restore the whole Enterprise
or Deployment server. Since this topic has been brought up, has anyone out
there tried to recover on NT Enterprise or deployment box? What issues did
you run into?

Thanks,

Crawford Winter
Senior Technical Analyst
Transamerica Life Canada
 
Crawford :

SQL backup tools are pretty good to backup databases to disk files or tapes,
and they work
well even while regular users are working. I use them everyday and haven't
had any trouble yet.
All my troubles were due to faulty tape drives or magnetic media.
If your DBs sum less than 50 Gb, you may keep backing them up with native
SQL commands such
as BACKUP DATABASE blabla TO TAPE='\\.\TAPE0' or BACKUP DATABASE blabla TO
DISK='C:\MYFILE.BKP'.
Typical DLT performance with native SQL tools is around 7 Gb per hour.
Arcserve, Veritas and Seagate tools provide much better performance;
nevertheless, you can
safely backup a 200 Gb with SQL commands, it just will take a loooong time
(about 30 hours!).
I've recovered NT/W2K Enterprise & Deployment servers from scratch about
twenty times, and
the most important point is to backup all SQL databases using SQL compliant
tools.
Don't try to backup SQL databases by shutting services down, copying
physical MDF and LDF files,
etc; that's the usual highway to DBA hell.
If you need further support, feel free to contact me :

Sebastian Sajaroff
[email protected]
Grupo ASSA, Argentina
 
Sebastian, thanks for the input. Are you saying that you are using the
Arcserve or Veritas SQL backup agents with no problems? I just wanted to add
that backing up SQL databases to file is a waste of disk space especially if
disk space is a premium concern. The problem with using the SQL backup
option to tape is that you don't get good tape management. In a large shop
like ours with 50+ servers, tape management is an issue.

Thanks,

Crawford
 
Re: RE: Restore of SQL 2K

Crawford,

there is a very good doc on KG about 'Swapping out a Deployment Server' Basically if you are keeping the same name, then swapping or restoring the Deployment server is a piece of cake. read the doc, it really is very good. I have had to do this a couple of times now and have never had any trouble.

Gordon

OneWorld Xe Sp16_018, W2k, SQL 2K on Compaq Proliant.
 
Re: RE: Restore of SQL 2K

Sebastian,

I am confused. Aren't sp_detach_db and sp_attach_db designed exactly for this purpose? I agree it may not be the best way to do things, but it does work.

Gordon

OneWorld Xe Sp16_018, W2k, SQL 2K on Compaq Proliant.
 
RE: RE: Restore of SQL 2K

These SP are used so that you can move you data file(s) and log file(s) to
different volumes

S
 
Re: RE: RE: Restore of SQL 2K

or to different servers viz...
"Detaching and attaching databases is useful if you want to move a database:

(1)From one computer to another without having to re-create the database and then restore the database backup manually...... " (SQL BOL)

OneWorld Xe Sp16_018, W2k, SQL 2K on Compaq Proliant.
 
RE: RE: Restore of SQL 2K

Gordon, do you detach all of your SQL databases nightly and run a tape
backup management system to backup your databases and then re-attach?

PS.. thanks for the Deployment server rebuild info!

Crawford Winter
Senior Technical Analyst
Transamerica Life Canada

b733 SP11, NT 4.0 SP6, SQL 7.0 SP2
 
Re: RE: Restore of SQL 2K

We're in the final stages of our DR testing here, and we abandoned ArcServe as being unreliable in the restore process (Actually, we never did figure out how to successfully restore the DB). Our plan on a server crash is to completely rebuild the servers from the ground up (We're a Dell shop and that way we're hardware independant in case we can't get exact replacement HW in or we have a spare server with sufficient capacity) and then restore SQL databases and the JDE code separately (make sure to reinstall the JDE services). As long as you have good documentation on where everything is installed, and can recreate the ODBC datasources, JDE runs just fine using this method (We've restored onto a desktop computer using this method and, while slow, it's functional!) Just make sure you reinstall your C++ to the same directory, otherwise there are a bunch of .ini files you have to change, as well as make sure the environment variables get created properly, but with a good network backbone (We're 100MB/s switches with gigabit between switches, fileserver is on same switch as Enterprise and Deployment) and a dedicated fileserver (Dell Poweredge 350's are CHEAP, and you said your shop had 50+ servers) we can backup to tape offline on the fileserver and still have fairly quick restoral times. The article on the KG about swapping out a Deployment Server is a great place to start, but needs quite a bit of tweaking. I'd recommend rebuilding the server as opposed to a full backup ala Tivoli or ArcServe, we've had problems with several large OSS packages not getting restored properly (Mostly registry entries or if there are even minor hardware changes such as swapping out a SCSI card with a different rev BIOS it can cause havoc.) Good luck!

- Scotto

B7332 sp10.1 SQL7.0 NT 4.0 sp5
 
Re: RE: RE: Restore of SQL 2K

Crawford,

the site I was previously at used to do an offline backup once a month and nightly full backups using SQL backup for the nightly backups and NT backup for the monthly file backups. It just meant that you had more than one option for the restore of your database. I would not recommend doing an offline backup each night - that would be impossible in most environments. BTW, if you are doing an offlie backup, just stop the SQL server services and then backup. There is no need to detach the databases.

I still think that the best option is like Sebastian and others have said. Backup using SQL and restore using SQL. Build the server from the ground up. It removes so many possible problems like registry and hardware incompatibilites. I like to see backups to disk as disk space is relatively cheap these days (although backing up 50 servers to disk could present problems!).

Here they are determined that they will restore using TIVOLI software and not build from the ground up in the event of a DR situation.

OneWorld Xe Sp16_018, W2k, SQL 2K on Compaq Proliant.<P ID="edit"><FONT SIZE=-1>Edited by antipodes1 on 1/23/02 07:33 AM.</FONT></P>
 
SQL 2k Backup/Restore

I believe the most reliable way to backup the a JDE SQL system is to use the native SQL Server tools. I have never had good experience with 3rd party tools.

1. Implement a SQL Server database maintenance plan that creates a full database backup nightly to the deployment server at the point where least system utilization occurs. Backup the transaction logs hourly onto the deployment server. No down time is incurred while these backups occur. The nightly job stream should be triggered to start after the nightly backup completes and can run concurrently with step 2 below.

2. Backup the deployment server to tape after the full backup of the SQL Server has been completed. This can occur during the day with little to no impact on end users. The biggest concern here is that a package build should not be occuring at the same time as the backup.

This scenario provides many restoration options. In the single server failure scenario no more than 1 hour of data would be lost. Assuming a proper tape rotation, in a data center wide disaster, no more than 24 hours of data plus tape time would be lost.



Jeremy Shearer
Independent CNC Consultant
[email protected]
 
Re: SQL 2k Backup/Restore

All the 3rd party tool use the Native SQL Backup all these agents do is
create a virtual device that interfaces into there backup engine

S
----- Original Message -----
From: "shearerj" <[email protected]>
To: <[email protected]>
Sent: Saturday, January 26, 2002 4:01 PM
Subject: SQL 2k Backup/Restore


the native SQL Server tools. I have never had good experience with 3rd party
tools.
database backup nightly to the deployment server at the point where least
system utilization occurs. Backup the transaction logs hourly onto the
deployment server. No down time is incurred while these backups occur. The
nightly job stream should be triggered to start after the nightly backup
completes and can run concurrently with step 2 below.
Server has been completed. This can occur during the day with little to no
impact on end users. The biggest concern here is that a package build should
not be occuring at the same time as the backup.
failure scenario no more than 1 hour of data would be lost. Assuming a
proper tape rotation, in a data center wide disaster, no more than 24 hours
of data plus tape time would be lost.
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OW&Number=2
7415
 
Back
Top