Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AlanS
Starting Member
28 Posts |
 Posted - 2004-03-24 : 12:00:19
|
| I have a backup job set up that backs up a database every Monday evening, and seems to be working properly. When I look at its Properties, click the Steps tab, highlight the single step, and click the Edit... button, I see that the code is as follows:BACKUP DATABASE [Production] TO [Monday Backup] WITH INIT , NOUNLOAD , NAME = N'Production Monday', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT select @i = position from msdb..backupset where database_name='Production'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='Production') RESTORE VERIFYONLY FROM [Monday Backup] WITH FILE = @iNow I'm trying to set up similar backup jobs for the other days of the week, but no matter what options I select in the Backup Wizard the generated code stops at the word NOFORMAT. What does the subsequent code do (I know I did not write it myself), is it desireable, and what do I need to select in the Backup wizard to have it included in new backup jobs? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-24 : 12:22:42
|
| Don't use the wizard! Your BACKUP command doesn't need to be so wordy. Just run soemthing like this:BACKUP DATABASE DBNameTO DISK = 'F:\MSSQL\BACKUP\DBName.BAK'WITH INITYou dont want STATS turned on as no one will be watching the backup. Not sure why you'd want the select statement or the restore command run either. Read up on BACKUP DATABASE in SQL Server Books Online. You'll learn a lot and not need to use wizards.Tara |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-24 : 20:24:31
|
| Note that the RESTORE is run with the VERIFYONLY command. Presumably this is to "verify" that you got a good backup. Anyone care to comment on the reliability of that? I'd bet it can take a while to run on large systems and you'd want to use something like Tara's script to validate your backups in other ways.--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-24 : 20:39:08
|
| Yeah, VERIFYONLY just verifies the validity of the file and not that it can actually be restored. TEST YOUR BACKUPS with RESTORE command on a non-production server!Tara |
|
|
|
|
|
|
|
|
question about code in backup job