Backup Location 1

[weigoldk]

I have a SQL database on Server1 (SQL 2005), but Server1 does not have a tape drive. I have a tape drive on Server2 and plenty of room for my backups.

The vendor suggested that I stop the SQL services on Server1, run my backup (both servers have Veritas backup, but neither have the SQL component) from Server2 then re-start the SQL services on Server1.

In reading through this forum, this doesn't sound like a great plan.

Some other unknown tech at the vendor set up the system and he/she already set up a maintenance plan in Microsoft SQL Server Management Studio to backup the database nightly.

Since the maintenance plan is already in place, I'd like to use it by backing up the results of the "Backup to Device" to Server2.

First, does my plan sound solid?

Second, I'm looking in the Microsoft SQL Server Management Studio and at the backup device, but can't see where the backups are. And, therefore, I don't know how to have Server2 come over and back the backups on Server1 up to tape.

The database is only used M-F from 8:00am to 5:00pm so there is NOT an issue with having 24x7 access to the database.

 

[ptheriault]

The vendor suggested that I stop the SQL services on Server1, run my backup (both servers have Veritas backup, but neither have the SQL component) from Server2 then re-start the SQL services on Server1.

That is a really bad idea.

How big is the db. If it's not to big I would backup your db locally on the server, then copy the .bak file to server 2 where you can write it to tape.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[weigoldk]

Thanks for the feedback Paul.

The database is under 3G and the bak file is under 45M but both will constantly grow.

Can I assume maintenance plan in Microsoft SQL Server Management Studio is creating the .bak file?

If that is the case, I need more explaination. The only difference that I see between my soluion and yours is you are doing a copy to Server2 (which will then be backed up) and I'm having Server2 come over and back up the file remotely. In my mind, both options will work, but if I have the backup do it, I eliminate copying time and space. If I'm missing something, please let me know. I'm just getting my feet wet with SQL admin.

I sincerely appreciate your feedback.

 

[ptheriault]

I always think it's a good idea to keep the most recent backup on your local server. You find that if you have to restore a db it usually because of user error. So the restore is faster if you don't have to go to tape. you just get it off of the local disk. But the way you suggested will certainly work fine.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

Go to the maintenance plan (Management>Maintenance Plans), right click on it and select Modify. That will bring up the flow-plan. Find the step for the backups, right click on it and choose Edit. That is where you will find out the location of the backups. Once you find out the location, go there and see if the backups are done.

You then have a couple of options.

1. Have a backup program copy the backup files to the other server
2. Create a job that uses operating command XCOPY (or COPY) to copy the backup file to the other server.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875