http://www.mssqltips.com/tip.asp?tip=1486

For those of us who don't know scripting languages, can you specify what needs to be edited to reflect each database we want to backup, and anything else that might need editing?  The only obvious one, which is notated in the script, is where to put the log file.

Thanks a bunch for the how-to though!  Looks like a slam dunk once I know where to put the database name(s).

The line containing the script

SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN ('TEMPDB')

tells us which database not to include.  In this case, all databases will be backed up except for TEMPDB. To include a list of databases, simply remove the NOT in the script and specify your databases inside the parenthesis like this

 SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME IN ('database1', 'database2', 'etc') AND NAME NOT IN ('TEMPDB')

You would still need to exclude the TEMPDB database as you cannot run a backup command for that. Since I wanted every database to be backed up, it would be easier to just exclude the TEMPDB instead

I would strongly recommend learning TSQL scripting as this is essential to manage SQL Server well

 I am getting an error when I run the sqlcmd file.  The error is "Msg102, Level 15, State 1 - Incorrect syntax near 'S' - When I remove the sqlcmd from databaseBackup.cmd and run it independently, it works.  Any ideas?

Thank you very much! 

There should be a space between the instance name and the -E switch. Check out the documentation for sqlcmd on MSDN.  Here's a sample sqlcmd call for a server instance named TESTSERVER\TEST

sqlcmd -STESTSERVER\TEST -E -i"E:\SQL_Backup\scripts\backupDB.sql"  

Thanks very much for these scripts. I'm using SQL Server 2005 Standard Edition with the Maintenance Plan, but have adapted the VB script to delete the old BAK and TRN files once they've been moved onto tape. Thanks again!

It's great to hear that the tip is of great help.  Your feedback is very much appreciated. It would also be great if you could highlight some issues you want solved and we would be be happy to provide tips to resolve them

Okay - here goes, the next step in implementing this great little VB script.

I would like to embed the VB script as part of an SQL Server agent job, which I could then include as part of my maintenance plan.

So, I tried to create a new job with one step: an Operating System (CmdExec) step.

I clicked on OPEN on the Job Step Properties page, browsed to the location of the vbs file then selected it, and the code was automatically inserted into the main window of the Job Step Properties page, as such:

I didn't change any other settings,
and then tried to run the job.

Here's the error I got:

Any info or feedback you may have would be terrific - thank you very much.

 Best,

Simon

Use an ActiveX Script type and select VBScript in the option instead of Operating System (CmdExec)

Great - that worked perfectly, thank you so much.

In case anyone out there is playing along at home, I also modified one of the conditions to check for TRN files (transactionl log files) as well as BAK file to delete.

            'Check if the file extension is BAK or TRN
            If (uCase(b)="BAK") Or (uCase(b)="TRN") Then

 Thanks again, this tutorial and all the help have been terrific - much appreciated.

- Simon

Hello, All: 

Would there be any way to have the script look through nested directories to delete backup files in them as well?

In other words, instead of just deleting files in the root of the sFolder variable, I'd like the script to search through sub-directories in sFolder as well.

 As always, thanks in advance for any input/suggestions.

-Simon

Try out this script and customize according to your need.

 'check if the file extension is BAK
  If uCase(b)="BAK" Then
          msgBox objFile
          intFileCount= intFileCount+ 1
 End IF
   Next
  
  
   ' Loop through all subdirectories
   Set objSubdirs = objFolder.SubFolders
   For Each objLoopFolder In objSubdirs
      CheckFolder objLoopFolder.Path
   Next
  
   Set objSubdirs = Nothing
   Set objFolder = Nothing
  
End Sub

Can you help one more person?

These scripts work terrific.  I have only had an issue on one instance that has Sharepoint databases with lots of dashes in the database name.

It backs up all the databases without dashes perfectly, but throws an error on the others:

Msg 102, Level 15, State 1, Server MSOCSP02\OFFICESERVERS, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Server MSOCSP02\OFFICESERVERS, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I tried putting in a semicolon after your SELECT @SQL = 'BACKUP DATABASE '+ @DBNAME +' TO DISK = ''D:\SQLBack\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT'; line.  This is not working though.  Thanks in advance for your help!

Laura, trying putting brackets around the @dbname variable 

Something like this

SELECT @SQL = 'BACKUP DATABASE ['+@DBNAME+'] TO DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT'