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'

Thanks so much ya'll / youse, depending on what region you are from.  That worked perfectly!

Appreciate your time in responding, thanks a million.

Great to hear it worked.  Feel free to post questions on the forum for anything related to SQL Server

This is good but would be very good if it wrote to the event log in the event of a failure, automatic monitoring would then alert admins to the problem. Have been trying to achieve this with little success.

I have a script that logs to a text file and a script that reads the text file for errors. It then sends an email notification should the job fails. I have one for VBScript and one for PowerShell. I'll work on an article for that if you provide me with the specifics of what you wanted done :-)

I have created a stored procedure (see below) and use following to call it:

sqlcmd -E -o d:\test\SQLBK_backup.txt -q "EXIT (exec mydb.dbo.mybackup  @drive='D',@inst='DEFAULT')"

I want to be able to use EVENTCREATE to generate an error if any of the backups fail, I the tried to use EXEC @RC= but this does not seem compatible with the EXEC (@SQL) so I assume I am missing the point somewhere!

ALTER PROCEDURE [dbo].[mybackup]
 -- Required parameters
 @drive varchar(1)=NULL,
    @inst varchar(25)=NULL

AS
DECLARE @ident INT, @sql VARCHAR(1000), @dbname VARCHAR(200), @datestr varchar(100)
set @datestr = DATENAME(YEAR,GETDATE())+ DATENAME(MONTH,GETDATE())
set @datestr = @datestr + RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR),2)
set @datestr = @datestr + RIGHT('00' + CAST(DATEPART(hour,GETDATE()) AS VARCHAR),2)
set @datestr = @datestr + RIGHT('00' + CAST(DATEPART(minute,GETDATE()) AS VARCHAR),2)
/* incremental backup of databases where recovery model is 'Full' or 'Bulk_logged' */
SELECT @ident=MIN(database_id) FROM SYS.DATABASES
WHERE [database_id] > 0
AND (recovery_model_desc='FULL' or recovery_model_desc='BULK_LOGGED')  
AND NAME NOT IN ('MODEL')

WHILE @ident IS NOT NULL
BEGIN
 SELECT @dbname = NAME FROM SYS.DATABASES
 WHERE database_id = @IDENT
 SELECT @sql = 'BACKUP LOG '+@DBNAME+' TO DISK = '''+ @DRIVE + ':\SQLBK\'
   + @INST + '\' + @DBNAME + '_' + @datestr + 'log.trn'''
 EXEC (@sql)
 SELECT @ident=MIN(database_id) FROM SYS.DATABASES
 WHERE [database_id] > 0
 AND database_id>@ident
 AND (recovery_model_desc='FULL' or recovery_model_desc='BULK_LOGGED')
 AND NAME NOT IN ('MODEL')
END

/* full backup of all databases */
SELECT @ident=MIN(database_id) FROM SYS.DATABASES
WHERE [database_id] > 0
AND NAME NOT IN ('TEMPDB')

WHILE @ident IS NOT NULL
BEGIN
 SELECT @dbname = NAME FROM SYS.DATABASES
 WHERE database_id = @ident
 SELECT @sql = 'BACKUP DATABASE '+@DBNAME+' TO DISK = '''+ @DRIVE + ':\SQLBK\'
    + @INST + '\' + @DBNAME + '_' + @datestr + 'full.bak'''
 EXEC (@sql)
 SELECT @ident=MIN(database_id)
 FROM SYS.DATABASES WHERE [database_id] > 0
 AND database_id>@ident
 AND NAME NOT IN ('TEMPDB')
END
; 

Thank you for the tip, it works great! Do you have tip on doing restore of the backup files as well..?

Best Regards,

janodega

This tip has been posted sometime this week.  Check it out if it meets your requirements

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

Thanks for the great script !

Is it possible to include a "RESTORE verifyonly" to this job ? I'm stuck ;)

In this section of the T-SQL code:

BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString + '.BAK'' WITH INIT'
   EXEC (@SQL)
   SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND database_id>@IDENT AND NAME NOT IN ('TEMPDB')
END

Change it to this:

BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT
/*Change disk location here as required*/
   SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT'
   EXEC (@SQL)
  
   SELECT @SQL = 'RESTORE VERIFYONLY FROM DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString +'.BAK'''
   EXEC (@SQL)

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

Thanks a lot. This makes sense.

It works fine, but how to restore from these backups? (Are the ldf files included in the backup?) 

 The backup files include the database files (MDF, LDF, NDF, etc.) Although this article is for SQL Server Express, it works with just about any edition.  So your restore process would include a RESTORE DATABASE command or Management Studio

Gentelmen, you are wasting your time. Why spend it on such a trivial task as DB backup - it could be achieved way better by off the shelf product like SQLBackupAndFTP. If it is just for fun - fine - as much as you want! But if you get paid for it - you are not effective and wasting money.

just needed customization on local setting and works great..

Another option is to try SQL Backup Master, a free tool for creating SQL Server backups.

http://www.sqlbackupmaster.com/

Nice article,

Used it on different occations and works really well.

I fancy the 'prefer the 'RESTORE VERIFYONLY FROM DISK' version.

This way your backups are 100% reliable.

Thanks for the article.

Hi Wesley,

I'm with you on using the RESTORE VERIFYONLY FROM DISK as a way to validate. However, I'm a bit paranoid when it comes to backups: the only way for me to trust them is when I have successfully tested the restore :-)

Its always a good thing to restore your backups on a preffered scheduled basis. So you have an emergency plan and know what to do in an emergency situation. If you are uncertain or paranoid you could restore your backups in a different instance so you can test your data or use it on a test enviroment. Beats waiting for an emergeny to accure. Good luck  

REM Run TSQL Script to backup databases
sqlcmd -S-E -i"E:\SQL_Backup\scripts\backupDB.sql"

REM "Below is the modified sqlcmd string to backup databases"

sqlcmd -S INSTANCENAME -i"D:\Program Files\MSSQL.1\MSSQL\Backup\backup.sql"

Hi there,

I stumbled across your script as we have an SQL Express version hosting a Sharepoint setup and I wanted a backup of the DB's.

However upon running the sql script i get an error as shown below

Processed 360 pages for database 'master', file 'master' on file 1.

Processed 2 pages for database 'master', file 'mastlog' on file 1.

BACKUP DATABASE successfully processed 362 pages in 0.364 seconds (8.146 MB/sec).

Processed 152 pages for database 'model', file 'modeldev' on file 1.

Processed 3 pages for database 'model', file 'modellog' on file 1.

BACKUP DATABASE successfully processed 155 pages in 0.438 seconds (2.889 MB/sec).

Processed 6632 pages for database 'msdb', file 'MSDBData' on file 1.

Processed 6 pages for database 'msdb', file 'MSDBLog' on file 1.

BACKUP DATABASE successfully processed 6638 pages in 3.591 seconds (15.141 MB/sec).

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, 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.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, 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.

Processed 1128 pages for database 'WSS_Search_TGNET5', file 'WSS_Search_TGNET5' on file 1.

Processed 1 pages for database 'WSS_Search_TGNET5', file 'WSS_Search_TGNET5_log' on file 1.

BACKUP DATABASE successfully processed 1129 pages in 0.885 seconds (10.450 MB/sec).

Processed 1928 pages for database 'WSS_Content', file 'WSS_Content' on file 1.

Processed 2 pages for database 'WSS_Content', file 'WSS_Content_log' on file 1.

BACKUP DATABASE successfully processed 1930 pages in 1.461 seconds (10.820 MB/sec).

Processed 1344 pages for database 'WSS_Content_4d1d522f951d4ac3b4326c3fb30285a6', file 'WSS_Content_4d1d522f951d4ac3b4326c3fb30285a6' on file 1.

Processed 2 pages for database 'WSS_Content_4d1d522f951d4ac3b4326c3fb30285a6', file 'WSS_Content_4d1d522f951d4ac3b4326c3fb30285a6_log' on file 1.

BACKUP DATABASE successfully processed 1346 pages in 0.917 seconds (12.019 MB/sec).

Processed 1344 pages for database 'WSS_Content_43b1b6fa6d0646eabffbc6950df4995c', file 'WSS_Content_43b1b6fa6d0646eabffbc6950df4995c' on file 1.

Processed 2 pages for database 'WSS_Content_43b1b6fa6d0646eabffbc6950df4995c', file 'WSS_Content_43b1b6fa6d0646eabffbc6950df4995c_log' on file 1.

BACKUP DATABASE successfully processed 1346 pages in 0.952 seconds (11.580 MB/sec).

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, 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.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, 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.

Processed 13048 pages for database 'WSS_Search_TGNET5_57a1b1d0040f437587dc951113cbf707', file 'WSS_Search_TGNET5_57a1b1d0040f437587dc951113cbf707' on file 1.

Processed 8 pages for database 'WSS_Search_TGNET5_57a1b1d0040f437587dc951113cbf707', file 'WSS_Search_TGNET5_57a1b1d0040f437587dc951113cbf707_log' on file 1.

BACKUP DATABASE successfully processed 13056 pages in 7.704 seconds (13.882 MB/sec).

Processed 43152 pages for database 'WSS_Content_c150bdefc0134f209ae7c123c7c55f10', file 'WSS_Content_c150bdefc0134f209ae7c123c7c55f10' on file 1.

Processed 2 pages for database 'WSS_Content_c150bdefc0134f209ae7c123c7c55f10', file 'WSS_Content_c150bdefc0134f209ae7c123c7c55f10_log' on file 1.

BACKUP DATABASE successfully processed 43154 pages in 25.643 seconds (13.786 MB/sec).

I am not a SQL guru by any means so any help gratefully recieved on how to overcome this error as not all the DB's are being backed up.

Thanks

Andy

@Andy Hetherington

You probalby need to be [ ]  around the @DBNAME like the following:

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

Greg is correct. Since the names of the SharePoint databases have funky GUID values with minus signs in them, you need to enclose the database names around the square brackets

@Greg & bass_player - thanks very much that did the trick :)

Hi All,

Sorry I have another query - I am successfully backing up our Sharepoint DB's using the above tweak.

I have now attempted to backup a Database under different instance but on the same SQL server that hosted the sharepoint yet I get a Named Piped Provider: Could not open a connection to SQL Server [53] error when running the Sqlcmd

Any idea what could be causing this error?

Thanks

Andy