Backup database file with date and time

1

Hello All,

I manage to make a batch file to take my database full backup and schedule it with windows backup utility for automatically backup. It running successfully but append new full backup on last taken full backup. I like to add time and date to make my backup unique but i am unable to do this. searched on google but still no hope. below is the command i run to take backup:
Sqlcmd -S machinename\sqlxpress -Q "BACKUP DATABASE databasename TO DISK = '\x.x.x.x\folder_name\xxx.bak'"
Need to add time and date with this backup file. Need your help.

2

I recommend you have a look at Minion Backup

http://minionware.net/backup/

its free, and is the easiest-to-install-and-run that I have seen knocking about.

Your home-brew will need a number of things, some of which are somewhat challenging to do.

Date/Time in the file name so each backup is a separate file. I reckon to do that you are going to have to launch the Backup from within SQL (I've built one, it took me a while to figure everything out), or use some fancy PowerShell script (which is fine, if you are familiar with PowerShell)
Need to also backup the LOG file, for any database that is in Full Recovery model (you'll get an error if you attempt to do that on a database in Simple recovery model, so it needs to be "aware" of the settings on the database)
Need to delete backup files after a retention period expires. You can do that using DOS CMD, based on the date of the file, but that is a bit of a blunt weapon. We retain LOG backups for a relatively short time (3 days), DIFF backups for 1 week, and the FULL backups which are made on a Sunday for 4 weeks. Its hard to do that at the DOS CMD level ...
You might like to copy Backup files (immediately, as soon as they are created) to A.N.Other Server/Location to guard against catastrophic server/disk failure, and if you have a remote server available that would also guard against total loss of the building / an exclusion-zone being thrown up around your office.

Then there are other housekeeping tasks, such as Rebuilding Indexes (which Minion also has solutions for)

3

Here is my backup script

`--select left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
declare @path1 nvarchar(300)
declare @path2 nvarchar(300)
declare @path3 nvarchar(300)
declare @path4 nvarchar(300)
declare @path5 nvarchar(300)
declare @path6 nvarchar(300)
declare @path7 nvarchar(300)
declare @path8 nvarchar(300)
declare @path9 nvarchar(300)
declare @path10 nvarchar(300)
declare @path11 nvarchar(300)
declare @path12 nvarchar(300)
declare @path13 nvarchar(300)
declare @path14 nvarchar(300)
declare @path15 nvarchar(300)
declare @path16 nvarchar(300)
declare @path17 nvarchar(300)
declare @path18 nvarchar(300)
declare @path19 nvarchar(300)
declare @path20 nvarchar(300)
set @Path1 = 'C:\SQLBackup\SpProductionDb' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path2 = 'C:\SQLBackup\SpProductionDb_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path7 = 'C:\SQLBackup\ReportServer' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path8 = 'C:\SQLBackup\ReportServer_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path9 = 'C:\SQLBackup\ReportServerTempDB' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path10 = 'C:\SQLBackup\ReportServerTempDB_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path11 = 'C:\SQLBackup\privatecall' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path13 = 'C:\SQLBackup\privatecall_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path12 = 'C:\SQLBackup\signeddocument' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path14 = 'C:\SQLBackup\signeddocument_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path15 = 'C:\SQLBackup\grievances' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path16 = 'C:\SQLBackup\grievances_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path17 = 'C:\SQLBackup\expenses' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path18 = 'C:\SQLBackup\expenses_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path5 = 'C:\SQLBackup\issueTrack' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path6 = 'C:\SQLBackup\issuetrack_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path19 = 'C:\SQLBackup\lockbox' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'
set @Path20 = 'C:\SQLBackup\lockbox_Log' + left(replace(REPLACE(CONVERT(VARCHAR(40),GETDATE(),120),':','-'),' ','_'),16) +'.bak'



Backup Database SPProductiondb to Disk = @Path1
backup log SPProductiondb to Disk = @Path2
Backup Database issuetrack to Disk = @Path5
Backup log issuetrack to Disk = @path6
Backup Database ReportServer$SQLEXPRESS to Disk = @Path7
Backup Database ReportServer$SQLEXPRESSTempDB to Disk = @Path9
Backup Database privatecall to Disk = @Path11
Backup log privatecall to Disk = @Path13
Backup Database signeddocument to Disk = @Path12
Backup log signeddocument to Disk = @Path14
Backup Database grievances to Disk = @Path15
Backup log grievances to Disk = @Path16
Backup Database expenses to Disk = @Path17
--Backup log expenses_log to Disk = @Path18
Backup Database lockbox to Disk = @Path19
--Backup log lockbox_log to Disk = @Path20`
4

I think there is "risk" in rolling-your-own at a "fairly simple" level.

  • Add a new database

You have to remember to modify the backup script, and you have to do it right. ("Assuming" that the backup is working is no good to you if there is a typo in the Path / Filename such that you have NO backups :frowning: ) Testing each newly added backup is trouble enough ...

Our backup automatically adds any new databases to the backup process; if the database is ReadOnly / OffLine / has/does not have LOGs, whatever, it takes care of it. It took us A LOT OF TIME to build the script to that level of sophistication. Minion, and others like it, takes care of all that stuff ...

Our backup avoids backing up some databases. For example, if the name of the DB starts "RESTORE_" it is excluded - we use that when we want to restore a database temporarily, so it doesn't automatically start backing up!

Someone puts an Extended Character, or a backslash, in a database name. Now a simple "filename is based on the database name" script won't work (and it may fail silently, then you have no backups)

  • backup log SPProductiondb to Disk = @Path2

The Log ought to be backed-up at a different frequency to the Full backups. We backup our LOGs every 10 minutes, for a database in Full Recovery Model I don't see ANY reason to do it less often - WHEN your database becomes corrupted you will want the smallest / shortest loss that is possible. Maybe 10 minutes, even, is too long.

  • C:\SQLBackup...

What if you want to move the backups somewhere else? A configurable system makes that easier (compared to a script that has everything hard-wired)

What about purging old files? Copying them to another location? And so on ...

I would suggest that you should be using Compression. Available (in newer releases of SQL) in even the most basic versions; it dramatically cuts down the amount of I/O - that saves time on the backup, saves disk space of course, but also very importantly it saves time on a restore - restoring all the DBs onto a new server, because the old one failed, can take long enough to become a problem for Users / the Business, and restoring from Compressed backups saves a lot of time.

Should also have Checksum turned on (i.e. for safety) and a variety of other things.

Hence why I think it is a high risk strategy to have a "simple" script for backup. Let someone else who has considered all these things do it for you ... each version of SQL that comes out causes us to have to spend a considerable time enhancing our script; often we only find out about things by accident - like the fact that Compression is a) a really important feature and b) had become available in non-Enterprise versions. I haven;t yet decided that we will change to someone else's script, but it is definitely borderline whether it is cost effective, and SAFE!!, for us to use our own in-house script (we have dozens of SQL Servers and hundreds of databases)

5

P.S. I recommend that you add MASTER and MSDB to your backup script.

6

I was simply showing how to put the date on a file as the op asked, but I was unaware of minionware, it looks very cool. I may install that myself.

Thanks!

7

Understood, and sorry if my post appeared confrontational. I've seen a lot of situations where people's backups are ineffective (for all sorts of reasons), when they come to restore, and just wanted to suggest that taking a well trodden path might be safer, in the long run.

8

Although I love and use xp_CmdShell extensively, many people do not allow it to even be turned on. I've also heard rumor that Minion uses xp_CmdShell. That certainly doesn't make it bad but it won't work if you have a policy to not allow xp_CmdShell to be turned on.

As for roll-you-own backups, I always roll my own. I certainly don't hard code database names or paths, though.

9

That's true

Good point Jeff, thanks.

Me too, but I always have (and mine definitely need xp_CmdShell too!).

If I was starting today, and knew that Minion and Ola Hallengren scripts exists, there is no way I would write my own - given the knowledge that I (now!) have of how long it took to develop a robust system, and the associated risk that a bug in my own backup code might leave me High and Dry!

IMHO Ola Hallengren scripts are very complex. They allow addressing every option that BACKUP provides, but I think they are way too complex for a simple situation - great for a DBA though - whereas Minion is simple to install and, once installed, if you do no config at all it will start making regular backups using default settings that will at least mean that the databases are getting backed up "good enough"

I should ask them for commission ... :slight_smile: Mind you, what;s 10% of $0? :frowning: