db backups appending / overwriting

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

SQL Server Administration (2005)

db backups appending / overwriting

Author

Topic

jhermiz

3564 Posts

Posted - 2007-05-09 : 11:04:55

When I create a db backup on our network using BACKUP DATABASE...


BACKUP DATABASE [TKKCommonData] TO  DISK = N'G:\SQL_BACKUPS\TKKCommonData\TKKCommonData_DATA.bak' WITH NOFORMAT, NOINIT,  NAME = N'TKKCommonData_DATA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

I've specified the NOINIT so that it appends rather than overwrites the database, however the database is still overwritten.
Any idea how to get the database to backup and append to the set rather than overwrite the backup ?

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-09 : 11:16:09

With noinit, that's it as I know.

jhermiz

3564 Posts

Posted - 2007-05-09 : 11:41:08

quote:
Originally posted by rmiao

With noinit, that's it as I know.

That is what I specified but it is overwriting the database.
I would rather it do something like TKKCommonData_Todays_Date.BAK
or even TKKCommonData_1.bak, TKKCommonData_2.bak, etc.

As you see I have included the NOINIT option but I am getting an overwrite in any event. Is this for tape backups only ? I know in 2000 it did not work like this ?

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-09 : 11:46:29

No, it works for disk. I didn't have problem with that option.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-09 : 11:57:05

When I run your command but changing the names it appends rather than overwriting.
Is there something odd about the datetime on the server which causes it to expire?
Do you have sp2?
Can you write to separate files including the the datetime in the name instead - that's what I always do.

Try just running
backup database master to disk='c:\bak.bak'

a few times and see if the file gets bigger and the file sequence in the message increases.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 12:55:00

Are you sure you want to be Appending, rather than generating Unique names (like your proposed "TKKCommonData_Todays_Date.BAK")?

Personally I want individual files for each of my backups to reduce the chance of collateral damage from one to another ...

Kristen

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-05-09 : 13:27:54

Is the file growing? What do you get from


restore headeronly from DISK = N'G:\SQL_BACKUPS\TKKCommonData\TKKCommonData_DATA.bak'

I would expect you get one row per backup contained in the file.

jhermiz

3564 Posts

Posted - 2007-05-09 : 15:55:20

quote:
Originally posted by Kristen

Are you sure you want to be Appending, rather than generating Unique names (like your proposed "TKKCommonData_Todays_Date.BAK")?

Personally I want individual files for each of my backups to reduce the chance of collateral damage from one to another ...

Kristen

Err you did correct me kristen, my terminology was definately off.
What do you suggest...can I do something like that ?
If so how to change this:

BACKUP DATABASE [TKKCommonData] TO DISK = N'G:\SQL_BACKUPS\TKKCommonData\TKKCommonData_DATA.bak' WITH NOFORMAT, NOINIT, NAME = N'TKKCommonData_DATA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Can I do something like:

BACKUP DATABASE [TKKCommonData] TO DISK = N'G:\SQL_BACKUPS\TKKCommonData\TKKCommonData_DATA_' + GetDate() + '.bak' WITH NOFORMAT, NOINIT, NAME = N'TKKCommonData_DATA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

I am running SP2 by the way.

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-09 : 16:03:53

You can check out the code that I use to build the file name in my backup stored procedure. Or you could just use my stored procedure to perform the backups.

http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 16:07:05

The following SQL, allowing for some defined variables, is valid as-is - so you don't have to mess with string concatenation in order to do an EXEC (@strSQL) and all that guff!


BACKUP DATABASE @strDB 
	TO DISK = @BackupFile
WITH	
	DESCRIPTION = @Description,
	MEDIADESCRIPTION = @Description, 
	MEDIANAME = @MediaName,
	NAME = @Name,
	STATS = 10

which leads to something like:


SELECT @BackupFile = 
	  @strRootPath
	+ db_name()
	+ '_'
	+ CONVERT(varchar(8), GetDate(), 112)				-- yyyymmdd
	+ '_'
	+ REPLACE(LEFT(CONVERT(varchar(8), GetDate(), 108), 5), ':', '')	-- hh:mm:ss
	+ '.BAK',

Kristen

jhermiz

3564 Posts

Posted - 2007-05-10 : 08:50:47

quote:
Originally posted by tkizer

You can check out the code that I use to build the file name in my backup stored procedure. Or you could just use my stored procedure to perform the backups.

http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspx

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Thanks folks, you're all lifesavers :).

Programmers HowTo's -- [url]http://jhermiz.googlepages.com[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources