Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft SQL Server: Setup and Administration FAQ


Backing up your database and keeping multiple copies around. by mrdenny
Posted: 28 Feb 05 (Edited 19 Mar 10)

Everyone should backup their databases.  Everyone should also keep more than one copy of their backup handy in the event that the most recent backup is bad when you go to restore it.

One method is to use the Database Maintenance Plans.  However these have there own set of problems.  sqlmaint.exe can fail for any number of reason, none of which are well documented.  (Not to mention that the logs suck.)

The following code will backup your databases using T/SQL with a different filename for each backup file.  We will then use robocopy to move the older backups to a temporary directory then delete the files within the directory.

The parts that are in blue are the bits of code that you will need to modify to make this work.

These procedures need to be created in your Master database.


use master
create procedure usp_BackupDatabase
    @DatabaseName varchar(100)
declare @date varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)
set @BackupPath = 'd:\Some\Path\'
set @date = convert(varchar(10), getdate(), 112)
set @BackupFile = @BackupPath + @DatabaseName + '_' + @date + '.bak'
backup database @DatabaseName to disk=@BackupFile
create procedure usp_BackupLog
    @DatabaseName varchar(100)
declare @date varchar(10)
declare @time varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)
set @BackupPath = 'd:\Some\Path\'
set @date = convert(varchar(10), getdate(), 112)
set @time = replace(convert(varchar(10), getdate(), 108), ':', '')
set @BackupFile = @BackupPath + @DatabaseName + '_' + @date + '_' + @time + '.trn'
backup log @DatabaseName to disk=@BackupFile
Now create a couple of jobs.  One to handle your full backups nightly.  It will run the following code.


sp_MSForEachDB 'master.dbo.usp_BackupDatabase ?'
And one to handle your log backups every hour (or how ever often you need to back then up).


sp_MSForEachDB 'master.dbo.usp_BackupLog ?'
If you need to backup a database by hand, simply run this.


exec master.dbo.usp_BackupDatabase '{UserDatabaseName}'

Now to keep your hard drive from filling up with old backup files you'll need to purge the old backup files nightly.  I recommend keeping them for at least 3 days.  I recommend using robocopy (available from Microsoft as part of the resource kit) to handle this.

Create a batch file to handle this.  Place it within your SQL servers binn folder (Usually c:\program files\microsoft SQL Server\80\tools\binn) (If SQL 7 c:\program files\microsoft SQL Server\70\tools\binn).  You'll also need to create a folder on the drive with the backup files called TempDelete (I'll be using the D drive for this example).  The batch file should look like this.


robocopy d:\some\path d:\TempDelete /MOV /MINAGE 3
dir /B /S /A-D d:\tempdelete > dellist.txt
@for /f "tokens=1" %%a in (dellist.txt) del %%a /F /Q
The first line of this batch file moves all the files older than 3 days to the TempDelete folder. (You can change the number of days to keep by changing the MINAGE flag.)
The second line gets a list of all the files in the TempDelete folder and stores that list in a text file.
The third line deletes each file from the list created in step 2.

Schedule a job within the SQL Agent to handle running this batch file.

I personally recommend backing the databases up to a different server.  This way if the SQL Server completly dies, your backups are still safe.  However if you have good redundancy built into your disk arrays this isn't required.  These scripts will work fine over the network as well.

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close