October 17, 2007 at 4:08 pm
Hi,
I have a production database with 3 separate jobs that do the following:
full db backup nightly
diff backup every 6 hours
trn backup every 15 minutes
These are all copied off of the production sql server to a backup server when the backup step of each job completes.
I am testing these backups on a test server by restoring first the full, then the latest diff, then trns since the latest diff. in using the mgt studioi gui, is there an easier way of restoring the logs rather than restoring each one individually (i.e. restore, go point to the next one, restore with no recovery, point to the next one, restore with no recovery, etc.
Anyone have suggestions for a better maintenance plan without using the native maintenance plan wizard? The people who installed sql on this server did not install ssis so I can't use the maintenance plan wizard. Thanks so much for any suggestions!!
October 20, 2007 at 8:38 pm
If you've patched the server, you shouldn't need to install SSIS. I believe service pack 2 allows you to run the maintenance plans without SSIS.
Are these files always named the same thing? If so, it's easy to script the restores. If the names are always changing, then it's more complicated to determine what the name is and then in what order they should be restored.
October 22, 2007 at 2:16 pm
The names are always changing because I create them as the DBNAME_DATE_TIME.trn.
October 23, 2007 at 9:15 pm
I've done this before, but it requires some scripting in ActiveX/VBScript/SMO or something like that. You need to read the file system, then check for all the files. Since you've named the file names, you can scan through them for the next file you need to restore (based on time) and restore that one.
There are some scripts here to help you work this out:
http://www.sqlservercentral.com/scripts/Replication/31544/
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/31536/
October 24, 2007 at 1:45 pm
If you are restoring in a different server and if it is an ongoing process I would recommend LOG Shipping process to be setup.
Its easy to manage and you don't have to write the custome scripts to restore all your backups.
FYI - the secondary database will be in STANDBY if you are using logshipping.
Razi, M.
http://questivity.com/it-training.html
October 24, 2007 at 2:16 pm
Nope, this is simply for DR purposes so these scripts are a great starting place; thanks!
I have a question which is probably very stupid but I can't seem to get an answer that I understand. When using backup sets to manage backups; is the "set" one file that each backup whether full, differential, or trx log get saved to that file? I just want to make sure that if I implement backup sets I'm not putting all of my backup eggs in one basket. Does that make sense? I've always implemented backups using jobs or scripts but the gui in 2005 ( and i suppose 2000) for restoring backup sets looks handy but I don't want all of my backups appended to one file.
October 24, 2007 at 3:49 pm
The backup set depends on how you have used the backup file.
Example: only FullBackup.BAK or DifferentialBackup.BAK or TransactionalBackup.BAK or TRN.
If you have used GUI in SQL 2000 and SQL2005 there will be an option that you can set to Append to a file or overwrite existing file.
For transactional backup it creates a new file by default based on the frequency set.
Also if you are using manual script using INIT option in Backup command will overwrite the existing backup file.
Razi, M.
http://questivity.com/it-training.html
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply