Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Server Express Backups and Deletion of Older Backup Files


By:   |   Read Comments (43)   |   Related Tips: More > Express Edition

Problem

As a lot of line-of-business applications are being built with SQL Server 2005 Express Edition as their backend database, we need to make sure that we backup the system and the user databases running on these instances. Unfortunately, SQL Server 2005 Express Edition does not come with SQL Agent which we would normally use to create a database maintenance plan to backup all the databases. How do we perform a backup of our system and user databases in SQL Server 2005 Express Edition similar to how we do it in other editions?

Solution

We can use a combination of VBScript and TSQL with Task Manager in Windows to automate the creation of user and system database backups in SQL Server 2005 Express Edition.

Note: All files should be saved in folder E:\SQL_Backup\scripts.  This can be changed, but this example is setup for this folder.  If you save to a different folder you will need to update the scripts accordingly.

Step 1 - Create the TSQL script
The TSQL script below generates a database backup similar to the formatting generated by the database maintenance plan, taking into account the date and time the backup files were generated.  We save the script as a .sql file, E:\SQL_Backup\scripts\backupDB.sql, which we will call from a batch file using sqlcmd.

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)
--month variable
IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
   
SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))
ELSE
   SET 
@monthSTR'0' CAST(MONTH(GETDATE()) AS CHAR(2))
--day variable
IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   
SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))
ELSE
   SET 
@daySTR='0' CAST(DAY(GETDATE()) AS CHAR(2))
--hour variable
IF (SELECT LEN(DATEPART(hhGETDATE())))=2
   
SET @hourStr=CAST(DATEPART(hhGETDATE()) AS CHAR(2))
ELSE
   SET 
@hourStr'0' CAST(DATEPART(hhGETDATE()) AS CHAR(2))
--minute variable
IF (SELECT LEN(DATEPART(miGETDATE())))=2
   
SET @minStr=CAST(DATEPART(miGETDATE()) AS CHAR(2))
ELSE
   SET 
@minStr'0' CAST(DATEPART(miGETDATE()) AS CHAR(2))
--name variable based on time stamp
SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr @dayStr @hourStr @minStr
--=================================================================
DECLARE @IDENT INT@sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(database_idFROM SYS.DATABASES WHERE [database_id] AND NAME NOT IN ('TEMPDB')
WHILE @IDENT IS NOT NULL
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_idFROM SYS.DATABASES WHERE [database_id] AND database_id>@IDENT AND NAME NOT IN ('TEMPDB')
END

 

Step 2 - Create the VBScript file
Next, we will need to create a VBScript file which will be responsible for cleaning up old copies of the database backups. The script also writes to a log file which records the database backup files.

  • You do need to create an empty file named  E:\SQL_Backup\scripts\LOG.txt to save a log of the deleted files.
  • Also copy the below script and save as E:\SQL_Backup\scripts\deleteBAK.vbs
On Error Resume Next  
Dim 
fsofolderfilessFoldersFolderTarget    
Set fso CreateObject("Scripting.FileSystemObject")  

'location of the database backup files
sFolder "E:\SQL_Backup\"

Set folder fso.GetFolder(sFolder)  
Set files folder.Files    

'used for writing to textfile - generate report on database backups deleted
Const ForAppending 8

'you need to create a folder named "scripts" for ease of file management & 
'a file inside it named "LOG.txt" for delete activity logging
Set objFile fso.OpenTextFile(sFolder "\scripts\LOG.txt"ForAppending)

objFile.Write "================================================================" VBCRLF VBCRLF
objFile.
Write "                     DATABASE BACKUP FILE REPORT                " VBCRLF
objFile.
Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" VBCRLF
objFile.
Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" VBCRLF VBCRLF
objFile.
Write "================================================================" VBCRLF 

'iterate thru each of the files in the database backup folder
For Each itemFiles In files 
   
'retrieve complete path of file for the DeleteFile method and to extract 
        'file extension using the GetExtensionName method
   
a=sFolder itemFiles.Name

   
'retrieve file extension 
   
fso.GetExtensionName(a)
       
'check if the file extension is BAK
       
If uCase(b)="BAK" Then

           
'check if the database backups are older than 3 days
           
If DateDiff("d",itemFiles.DateCreated,Now()) >= Then

               
'Delete any old BACKUP files to cleanup folder
               
fso.DeleteFile a 
               objFile.WriteLine 
"BACKUP FILE DELETED: " a
           
End If
       End If
Next  

objFile.WriteLine "================================================================" VBCRLF VBCRLF

objFile.
Close

Set 
objFile = Nothing
Set 
fso = Nothing
Set 
folder = Nothing
Set 
files = Nothing

 

Step 3 - Create the batch file that will call the TSQL script and the VBScript file
We need to create the batch file which will call both the TSQL script and the VBScript file. The contents of the batch file will be a simple call to the sqlcmd.exe and a call to the VBScript file using either wscript.exe or simply calling the file. Save the file as E:\SQL_Backup\scripts\databaseBackup.cmd and save it in the scripts subfolder

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

REM Run database backup cleanup script
E:\SQL_Backup\scripts\deleteBAK.vbs

 

Step 4 - Create a task in Windows Task Scheduler
Create a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel -> Scheduled Tasks or under Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks.

Since we are using Windows authentication to run the TSQL script, use a Windows account that is a member of the db_backupoperator role of all the databases

  • Launch "Scheduled Tasks"
  • Click on Add Scheduled Task
  • Browse to the "E:\SQL_Backup\scripts" folder and select databaseBackup.cmd
  • Pick the frequency and time for the backups to run
  • Lastly, enter a Windows account that has at least db_backupoperator role privileges for all of the databases
  • See screenshots below

Next Steps
  • Implement this solution on your SQL Server 2


Last Update:






About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, April 14, 2016 - 12:43:48 PM - bass_player Back To Top

Hi Salmndr,

This works with SQL Server 2000. Just replace SQLCMD with OSQL. But you should no longer be on SQL Server 2000 nor SQL Server 2005 as both are no longer under Microsoft's extended support

Hi Ricky,

Thank you very much for the suggestion.


Thursday, April 14, 2016 - 8:14:31 AM - Ricky Back To Top

 Great solution, thanks for this:

Handy tip if you want to only backup one database, run this on the instance:

USE master
SELECT name, DB_ID(name) AS DB_ID
FROM sysdatabases
ORDER BY dbid

It will return the ID of the databases, pick the one you want, then change this part of the sql script backupDB.sql:

BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT

Swap @IDENT for your DB number:

BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = 5

 

 


Friday, January 30, 2015 - 11:55:53 AM - Salmndr Back To Top

Can I use this code with sql seerver 2000? I believe sqlcmd is not available in sql 2000. Is that right


Friday, January 24, 2014 - 5:38:03 PM - bass_player Back To Top

Hi Dan,

Because this is calling raw TSQL statetements, they will work with any version and edition of SQL Server. 


Friday, January 24, 2014 - 6:01:12 AM - Dan Back To Top

Can I use this on MS SQL Express 2008?

A list of supported versions might be helpful to other n00bs like me.


Thursday, May 09, 2013 - 10:20:18 AM - Andy Hetherington Back To Top

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


Wednesday, May 08, 2013 - 4:19:20 AM - Andy Hetherington Back To Top

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


Tuesday, May 07, 2013 - 1:28:38 PM - bass_player Back To Top

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


Tuesday, May 07, 2013 - 1:02:52 PM - Greg Robidoux Back To Top

@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'

 


Tuesday, May 07, 2013 - 10:13:29 AM - Andy Hetherington Back To Top

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


Tuesday, February 26, 2013 - 2:22:25 AM - Gobinda Back To Top

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"

 


Monday, February 11, 2013 - 5:36:44 AM - Wesley Back To Top

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  


Friday, February 08, 2013 - 10:41:16 AM - bass_player Back To Top

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 :-)


Friday, February 08, 2013 - 9:32:20 AM - Wesley Meijer Back To Top

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.

 


Thursday, January 17, 2013 - 9:49:52 AM - Mark R. Back To Top

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

http://www.sqlbackupmaster.com/


Monday, November 19, 2012 - 7:57:47 AM - jan Back To Top

just needed customization on local setting and works great..


Monday, August 30, 2010 - 11:03:29 AM - gil Back To Top
thanks! works perfectly!


Friday, June 05, 2009 - 12:15:27 PM - bass_player Back To Top

 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


Monday, May 18, 2009 - 3:08:38 AM - stefaan Back To Top

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

 

 


Monday, May 11, 2009 - 6:46:14 AM - berndn Back To Top

Thanks a lot. This makes sense.


Monday, May 11, 2009 - 6:18:38 AM - admin Back To Top

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


Monday, May 11, 2009 - 5:40:25 AM - berndn Back To Top

Thanks for the great script !

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


Saturday, November 15, 2008 - 12:58:26 AM - bass_player Back To Top

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

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


Thursday, November 06, 2008 - 2:34:34 AM - janodega Back To Top

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

Best Regards,

janodega


Wednesday, October 29, 2008 - 7:27:05 AM - mavina Back To Top

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

 


Monday, October 27, 2008 - 9:32:43 PM - bass_player Back To Top

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 :-)


Thursday, October 23, 2008 - 5:47:44 AM - mavina Back To Top

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.


Wednesday, August 20, 2008 - 10:14:38 PM - bass_player Back To Top

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


Tuesday, August 19, 2008 - 10:19:21 AM - LauraV Back To Top

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

Appreciate your time in responding, thanks a million.


Tuesday, August 19, 2008 - 3:01:09 AM - bass_player Back To Top

Something like this

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

 


Thursday, August 14, 2008 - 10:31:31 AM - aprato Back To Top

Laura, trying putting brackets around the @dbname variable 


Thursday, August 14, 2008 - 8:18:19 AM - LauraV Back To Top

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!


Thursday, May 29, 2008 - 5:59:56 PM - bass_player Back To Top

Try out this script and customize according to your need.


Dim strRootFolder
Dim intFileCount
Dim objFSO
getCount

Sub getCount()
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   intFileCount= 0
   strRootFolder = "D:\MSSQLTips\test\"
   CheckFolder strRootFolder
   msgBox "Total files in " & strRootFolder & ": " & intFileCount
End Sub

Sub CheckFolder(strPath)
   Dim objFolder
   Dim objFile
   Dim objSubdirs
   Dim objLoopFolder
  
   msgBox "Checking directory " & strPath
   Set objFolder = objFSO.GetFolder(strPath)
  
  
   ' Check files in this directory
   For Each objFile In objFolder.Files
 a=objFolder & objFile.Name
 b = objFSO.GetExtensionName(a)

 '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

 


Wednesday, May 28, 2008 - 12:47:32 PM - simondoubt Back To Top

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


Monday, May 26, 2008 - 6:33:13 AM - simondoubt Back To Top

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


Monday, May 26, 2008 - 4:03:30 AM - bass_player Back To Top

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


Wednesday, May 21, 2008 - 6:53:39 AM - simondoubt Back To Top

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


Tuesday, May 20, 2008 - 9:15:46 AM - bass_player Back To Top

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


Tuesday, May 20, 2008 - 7:21:40 AM - simondoubt Back To Top

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!


Friday, May 16, 2008 - 8:42:44 AM - bass_player Back To Top

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"  


Wednesday, May 14, 2008 - 4:18:03 PM - cainboot Back To Top

 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! 


Friday, May 09, 2008 - 1:30:45 AM - bass_player Back To Top

The line containing the script

SELECT @IDENT=MIN(database_idFROM SYS.DATABASES WHERE [database_id] 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_idFROM SYS.DATABASES WHERE [database_id] 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


Wednesday, April 30, 2008 - 5:09:09 PM - MrSato Back To Top

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).


Learn more about SQL Server tools