Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Backing up your database and keeping multiple copies around.

Backups

Backing up your database and keeping multiple copies around.

by  mrdenny  Posted    (Edited  )
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.
Code: 
use master
create procedure usp_BackupDatabase
	@DatabaseName varchar(100)
as
declare @date varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)
set @BackupPath = [color blue]'d:\Some\Path\'[/color]
set @date = convert(varchar(10), getdate(), 112)
set @BackupFile = @BackupPath + @DatabaseName + '_' + @date + '.bak'
backup database @DatabaseName to disk=@BackupFile
go
create procedure usp_BackupLog
	@DatabaseName varchar(100)
as
declare @date varchar(10)
declare @time varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)
set @BackupPath = [color blue]'d:\Some\Path\'[/color]
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
go
Now create a couple of jobs. One to handle your full backups nightly. It will run the following code.
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).
Code: 
sp_MSForEachDB 'master.dbo.usp_BackupLog ?'
If you need to backup a database by hand, simply run this.
Code: 
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.
Code: 
robocopy [color blue]d:\some\path d:\TempDelete[/color] /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.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top