May 5, 2011 at 10:41 am
Or can you explain the difference? I use .BAK for db backups and .TRN for trans. backups.
Also -does anyone see issues with this script? I will be testing a point of failure restore and want to be certain that my trans logs are being backup up properly.
USE myDB
GO
-- Declares a string
declare @sql nvarchar(255)
-- Declares the filename.
declare @bkdbName varchar(50)
set @bkdbName = 'myDB_LOG_BKUP_'
+ Convert(varchar(50),getdate(),110)
+ '_' + convert(varchar(2),DATEPART(HH,getdate())) + convert(varchar(2),DATEPART(MI,getdate()))
May 5, 2011 at 10:48 am
You can use whatever extension you want for backup (full, diff, log) files. Use .doc if you want to, SQL Server won't care.
The extension has no meaning, except making it easier to visually see the difference between them.
Personally I use bak for full backups, diff for differential and trn for log backups.
May 5, 2011 at 10:48 am
The file extensions are just there for your convenience, since you probably won't be double-clicking a backup file to try to "open" it. Use whatever you're comfortable with.
The script is just for generating a file name, right? If so, it looks okay to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2011 at 10:53 am
Thanks for the clarification. I did not paste the actual log command of my script.
-- Sets the backup running by executing the @sql string.
set @sql = 'BACKUP LOG myDB TO DISK = '''+@bkName+'.TRN'''
print @sql
exec sp_executesql @sql
May 5, 2011 at 2:04 pm
Extension is only for your refference there is nothing to do with SQL server. The only thing is you need to specify the name with extension (which you have given to the file at the time of taking backup like .BAK or .TRN) at the time of restoring the database.
So here your query is also correct.
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
May 5, 2011 at 3:05 pm
As with all of the previous posts it is all about making it simple and easy for you to identify the type of backup.
I have previously worked in a site that used a convention such as DBNAME_DB_YYYYMMDDHHMMSS.dmp - full backups
DBNAME_TRN_YYYYMMDDHHMMSS.dmp - Tlog backups
Have a look at your own companies naming standards or if there are not any a good opportunity to create some. ?
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 5, 2011 at 9:52 pm
I don't see any issue with your script. It looks fine. However, I have two suggestions:
1) Change USE myDB to USE Master.
2) You can use Maintenance Plan to take Log backup, if you don't have any specific reason to use customized script.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply