Database backups best practice

Question

Post reply

Database backups best practice

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 1

sharon (4/30/2008)
mike please can you explain with examples to make it more clear

Sharon,

1) The Weekly Full Backup job, which runs Sundays at 7 pm, has the following steps:

[font="Courier New"]backup database [MyDB] to [MyDB]

with init, name=N'MyDB', description=N'Weekly Full'

backup log [MyDB] to [MyDB_Log]

with init, name=N'MyDB', description=N'Init'[/font]

2) The Daily Differential Backup job, which runs Mon-Sat at 7 pm, has the following steps:

[font="Courier New"]backup database [MyDB] to [MyDB]

with differential, name=N'MyDB', description=N'Diff'

backup log [MyDB] to [MyDB_Log]

with init, name=N'MyDB', description=N'Init'[/font]

3) The "hourly" log backup runs every 15 minutess from midnight to 6:45 pm, Sun - Sat, and includes the following step:

[font="Courier New"]backup log [MyDB] to [MyDB_Log]

with name=N'MyDB_Log', description=N'Hourly'[/font]

The backup device can be created from the GUI, or with the following T-SQL:

[font="Courier New"]declare @DB Nvarchar(50)

declare @device Nvarchar(50)

declare @BackupFolder nvarchar(255)

declare @DeviceFile nvarchar(255)

set @DB = N'DB_Name'

set @BackupFolder = N'S:\SqlBackups\'

set @device = @DB

set @deviceFile = @BackupFolder + @device + '.Bak'

exec sp_addumpdevice @devtype = 'disk', @logicalname = @device, @physicalname = @deviceFile

set @device = @DB + N'_Log'

set @deviceFile = @BackupFolder + @device + '.Bak'

exec sp_addumpdevice @devtype = 'disk', @logicalname = @device, @physicalname = @deviceFile[/font]

Change @DB and the path for @BackupFolder to whatever will work in your environment. Makes a device for both MyDB and MyDB_Log.

For performance reasons, the backup works best if the destination is on separate media from the database files and logfiles. This is also desirable if your backup is to be used in case of a local drive failure.

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

Mike Hinds SSCarpal Tunnel Points: 4258 More actions · Answer 2

nkm129 (4/30/2008)
This is showing error
Incorrect syntax near '+'.

You will need something like this:

[font="Courier New"]declare @BakFile char(50)

set @BakFile = 'S:\SqlBackups\DbName '+convert(char(8),getdate(),112)+'.bak'

backup database DbName to disk = @BakFile[/font]

Again, this will append Year-Month-Day, but you will need to do additional work to clear out the colons if you are looking to add hours-minutes, as you would for a log backup that occurs more than once a day. Check out Books Online (BOL) for syntax of CONVERT for datetime types.

Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

amod.professional SSC Rookie Points: 27 More actions · Answer 3

hi,

I have data of 182 GB and ram 16gb with 8 logical cpus(2 quad core)

and [erfromance is very poort due to 13.45 GB of RAM is in used with sql server only and cache hit ratio is 90-85% every time.

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 4

amod.professional (2/21/2012)
hi,
I have data of 182 GB and ram 16gb with 8 logical cpus(2 quad core)
and [erfromance is very poort due to 13.45 GB of RAM is in used with sql server only and cache hit ratio is 90-85% every time.

Please start a new thread providing the information as requested in my signature.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 5

Here is how I parse the name of the backup file

Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount

Set @vNewpath = @vBackupPath

Set @vFileName = @vDBName + '_Full_' + REPLACE(REPLACE(REPLACE(Convert(Varchar(16), GetDate(), 120),'-',''),' ',''),':','') + '.bak'

Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + @vFileName +''''

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please note: 4 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prashanthreddygdwh SSC Rookie Points: 25 More actions · Answer 7

Could you please send the scripts to prashanthreddygdwh@gmail.com as mentioned in your earlier post? Thanks...

radu.gheorghiu Old Hand Points: 363 More actions · Answer 8

This is such a valuable thread! I have read all of your questions/answers and also searched on the internet for more possible best practices and I have wrote an article which tries to summarise all of these ideas and suggestions.

Please find my article here: http://sqlbak.com/blog/backup-best-practices/ and leave a comment if you consider that there is something else I might have missed. I hope this will help others and be a little time-saver.

Otherwise, this is a fantastic forum!

~ Just some guy trying to tune queries ~

maximo121 SSC Enthusiast Points: 161 More actions · Answer 9

maximo121

SSC Enthusiast

Points: 161

August 26, 2014 at 12:20 pm

#1741113

great! I needed this.

Max

itmanagers SSC Rookie Points: 35 More actions · Answer 10

Hi,

Hope you are doing fine.

I'm new using SQL backup jobs with multiple full, diff and log backups.

I have the following scenario

full- every day at 8:00pm

diff- every 4 hours

log- every 30 minutes

with full and diff I think there's no issues, however, I have the following issue with log backups,

every time a new log backup is executed, a new file of 6GB is created, for example if a log backup of 6,163,694 KB is created, the next log backup size is 6,165,294 kb is created

as far as I know there's an option to create log backups with only the size difference between them.

Could you please give a hand with this?

thanks in advance

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 11

itmanagers (8/27/2014)
Hi,
Hope you are doing fine.
I'm new using SQL backup jobs with multiple full, diff and log backups.
I have the following scenario
full- every day at 8:00pm
diff- every 4 hours
log- every 30 minutes
with full and diff I think there's no issues, however, I have the following issue with log backups,
every time a new log backup is executed, a new file of 6GB is created, for example if a log backup of 6,163,694 KB is created, the next log backup size is 6,165,294 kb is created
as far as I know there's an option to create log backups with only the size difference between them.
Could you please give a hand with this?
thanks in advance

Please start a new thread.