MSDE database backup is never overwritten, causing backup file to grow to unacceptable size

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 2000 Forums

SQL Server Administration (2000)

MSDE database backup is never overwritten, causing backup file to grow to unacceptable size

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-08 : 11:58:25

Daniel writes "If I run a backup from OSQL using the backup database dbname to disk = "X:\dbbackup.bak"

I get a nice little backup file just where it should be, the correct size and everything.

If I run the same command again, MSDE will backup the database to the same name and location but if I look at the size of the backup file it has noe DOUBLED in size.

My first question is why is MSDE not overwriting the previous backup file?

Second question is how do I get MSDE to backup the database with a date and time for a file name?

Third question is what happens if I try to restore from that huge backup file?

Forth question is has anyone else seen anything like this before?"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 13:22:32

Use WITH INIT option on your backup script.

BACKUP DATABASE whatever
TO DISK = '\\server\share\directory\whatever.bak'
WITH INIT

Second Question:

SELECT @filename = '\\server\share\directory\whatever_' + CONVERT(VARCHAR,GETDATE(),112) + '.bak'

BACKUP DATABASE whatever
TO DISK = '\\server\share\directory\whatever.bak'
WITH INIT

Third Question:

If you try to restore from it, you will need to choose which backup to actually restore.

Fourth Question:

Of course.

Fifth question (How do I do this right?):

Look at Tara's blog for the right way to do it. http://weblogs.sqlteam.com/tarad

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 13:44:31

Daniel, Mean Old DBA is getting older! :

Second Question:


DECLARE @filename nvarchar(256)
SELECT @filename = '\\server\share\directory\whatever_' + CONVERT(VARCHAR,GETDATE(),112) + '.bak'

BACKUP DATABASE whatever
TO DISK = @filename
WITH INIT

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 23:49:03

lol WOW!!! I definitely need a vacation.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 08:11:59

I think you need BossForum or WifeForum for that one!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources