To restore a single SQL Server 7 database ?

[StevenK]

We've been using VERITAS Backup Exec a number of weeks now for the backing up of SQL Server databases (from a list set-up including the 'master' database). We are now looking at the testing of the 'Restore' of one of our database backups (our system using the SQL Server database will soon be live and we need to ensure that our back-up processes are in place and trust-worthy). My intention was to test the restore of a single database (call it 'Database1'). Having read the instruction manual (or at least the part related to the restoring of SQL Server databases) I noticed the line : 'Using the SQL Agent, Backup Exec is able to backup the data contained in the database, but not the physical structure of the database. Consequently, databases must be manually rebuilt using SQL Server set-up or Rebuild Master to recover from a catastrophic failure.' Does this imply that I am only able to restore the data into a database that I create (using the scripts from the original database structure). Our plan is to recover the single database ('Database1') onto a seperate server and run our application against this for testing. Am I going to have to first create 'Database1' with the same structure on this server ?
Obviously for a full restore plan we should be restoring this database along with the controlling 'master' database - if this is done will we alos need to create the full structure as required for our 'Database1'.
I hope someone can shed some light on this for us ......
Thanks in advance.
Steve

 

[polymath5]

Steve, I would suggest that instead of relying on the SQL agent in Veritas. You might want to have SQL itself create the backups and then backup that BAK file. I currently use the SQL agent also, but for quite some time I've also been having SQL create backups on two seperate servers. Since these servers are backed up on their own tapes it eliminates single point of failure.'

The other advantage of having a SQL native backup on the same server, is that if you should need to restore, it is a lot faster than doing it by tape.

 

[Zelandakh]

Polymath5 is right.

Ditch the SQL agent. Use SQL wizard to dump the database/s to disk not tape. Use the backup folder in mssql. Then use Backup Exec to copy the backup folder to tape.

In the event of a small problem you can retrieve from disk (including easily into a parallel DB for testing. Catastrophic failure is from tape to disk then restore from disk. Takes very little time (1GB in 5 minutes from disk).

 

[StevenK]

Thanks for the suggestions of moving away from this method as our backup solution - this will be reviewed shortly. As our solution stands at the moment, is anyone able to give me pointers as to how we might look at running the restore of our single SQL Server database ('Database1') - do we have to have the database already created and feed the data (as backed up) into that ? Or does the restore cause the database to be created in the same instance ?
Thanks again
Steve

 

[polymath5]

Here's the Veritas KB article that describes step by step what is needed and what steps you must follow. TechNote ID: 192270

Seems pretty clear that you must rebuild the master and then restore whatever other databases you have.