Backup file types - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Backup file types

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2009-01-06 : 17:21:57

I'm a bit confused re backup file types.
If I do an online backup I get a file of type 'File'.
If I programmatically run the 'backup' function I get various file types - '27 File', '00 File', '18 file' etc.
Any relevant thoughts would be appreciated.

My word is my code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 17:25:36

There are 3 backup types: full backup, transaction log backups (also called incremental), and differential backups. You can backup these to a file, backup device, or to tape.

I don't know what you mean by "27 File", ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

iminore
Posting Yak Master

141 Posts

Posted - 2009-01-06 : 17:28:12

I'm doing a full backup.
When you look at the file in it's folder it says '27 File' under Type.

My word is my code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 17:30:27

I don't understand what you mean. What folder are you referring to? Could you post the exact command that you ran?

Or perhaps you've put an incorrect extension on the file? We typically use .bak for full backups and .trn for tlogs. I've seen .dif used for differentials but also have seen .bak used too and then diff would be in the first part of the name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

iminore
Posting Yak Master

141 Posts

Posted - 2009-01-06 : 17:33:54

This is my tsql code:

set @strsql = 'BACKUP DATABASE [' + @dbname + '] TO DISK = N''E:\' + @backupfolder + '\' +
@backupfile + ''' WITH RETAINDAYS = 30, NOFORMAT, INIT, NAME = N''' +
@dbname + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

exec sp_executesql @strsql

My word is my code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 17:35:14

Could you also show us the values you passed to those parameters, especially @backupfile? Or just do a PRINT @strsql and post that for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

iminore
Posting Yak Master

141 Posts

Posted - 2009-01-06 : 17:45:51

This is an example:

BACKUP DATABASE [dbname] TO DISK = N'E:\sqlbackups\dbname20090106_22.48.26' WITH RETAINDAYS = 30, NOFORMAT, INIT, NAME = N'dbname-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

My word is my code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 17:50:48

You'll notice that the filename is using an extension of 26 rather than the preferred bak, so that's why you are seeing that in Windows Explorer. Use a proper file name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

iminore
Posting Yak Master

141 Posts

Posted - 2009-01-06 : 17:55:19

Bloody Ada!
Next time you're in Brighton give me a shout - I'll take you on the dodgems.
Many thanks.

My word is my code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-06 : 18:01:26

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources