August 14, 2006 at 1:55 pm
Good day,
I have been tasked with coming up with a way for our engineers to be able to backup and restore their development databases on a whim, among other future uses. The request is as follows:
+++++++++++++++++++++++++++++++++++++++++++++++++++++
As examples for me to use and base more generic scripts, could you create the following stored procedures?
The calling format of each SP takes three parameters in the format:
utility_spBackupDB <FromDBHost> <catalog> ToPath
utility_spRestoreDB <ToDBHost> <catalog> FromPath
Where From/ToPath would be in the form .bak">\\Server\Share\Path\<catalog>.bak
So please use variables for the three parameters.
+++++++++++++++++++++++++++++++++++++++++++++++++++++
The FromDBHost and ToDBHost would be DNSnames, most of the time the local host, but not always...
The Catalog is the DBName, and the ToPath and FromPath are windows shares.
I guess my question has multiple questions really...
Can this be done? Has anyone done this? Is there something I should look at to get a good start? I have searched the scripts here, and I thought I had something, but I did not know about the DNSname requirement at that time.
Any thoughts / directions / links / anything will greatly help!
Cory
-- Cory
August 14, 2006 at 2:18 pm
Hi Cory
Check out SQL Books on Line (F1 from query analyser)
An Extract....
Backs up an entire database, transaction log, or one or more files or filegroups. For more information about database backup and restore operations, see Backing Up and Restoring Databases.
Backing up an entire database:
BACKUP DATABASE { database_name | @database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
similarly....
Restores backups taken using the BACKUP command. For more information about database back up and restore operations, see Backing Up and Restoring Databases.
Restore an entire database:
RESTORE DATABASE { database_name | @database_name_var }
[ FROM < backup_device > [ ,...n ] ]
I guess you can wrap these within which ever code you need.
Chris
August 14, 2006 at 2:39 pm
I did indeed check BOL, and google. I guess I needed to work the actual question more. The issue I will have is can this be done against any server, from one server in particular. As I have thought about this more, I think the FromDBHost will need to be designated from the place it is run from (in this case, OSQL), and the ToDBHost will need to be local...
The catalog and locations I am not all that concerned about, that should be easy to parameterize.
-- Cory
August 14, 2006 at 3:01 pm
You are correct. The Backup command has to be run with a connection to the server the database is located. The Restore command has to be run with a connection to the server you are trying to restore to. You'll need to prompt for the server name and database name for each action..
This is a tricky thing from a security thing as well. It also gets challenging when multiple people are accessing the same database. We actually got away from developers doing ad-hoc backup/restores all together. We keep our dev databases in full recovery mode and take trans log backups hourly during business hours. If they make a mistake and need to roll-back we can do so within a 1 hour window. Restores on the other hand need to be taken care of by a DBA in all cases. The developers actually like this system, it's less work for them and they don't need to remember to take the backup.
August 14, 2006 at 6:06 pm
Ideally, this is a solution would like as well...the problem is that each developer has their own dev enviroment, their laptops. I disagree with this, but so far, I have been unable to convince them other wise. Thanks for the ideas.
-- Cory
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply