Point in Time: "Append to Media" Vs "Overwrite an existing media" in SQL Ser

Dear All,

I know the basic defiinition of these two options, but i am not very clear why would someone choose one over another,
currently I am using Append to Media option, and every day backup, I see my backup files growing in size.

can someone give me nice example about these two options,

Thanks,

When you append to the media the previous backups also remain in the backup file. One backup file can contain numerous backups. When you overwrite the backup file is recreated every time so it contains just one file.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Thanks for your reply.

That means, overwrite backup media will occupy less space, then why would i choose Append to media,

any risk involved in overwriting option ?, becuase every body recommends appending, why not ovewritting.

Thanks,|||

It will definitely use less space.

I do not see the risk of overwriting your backup unless you need a full backup that was taken a couple of days earlier to be accessible. In most cases I suppose you will only be interested in the latest version of your database and not the one you had 3 months ago. For the 'special' occasions where a specific version may be required I would simply take a separate backup.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thanks, your advice is helpful,

but if i want to be able to recover point in time, then I have to go with append to media, along with Transaction long back , RIGHT?

|||

The risk is that if you overwrite the previous backup, and then the current one fails for any reason you are left with no backup for your database. Not a good situation.

What I'd recommend instead, which is a fairly common practice, is to do each backup to a new file which incorporates the current date/time as part of the filename.

That way you always have unique filenames, you can easily prune out old backups, and it's obvious which backup is from what time.

|||

Thanks,

but even if i append to media, and it fails, i will left with no backups , Right?

also for your best suggestion, can you send me sample query or how to do in Enterprise manager.

|||Fahim,
If you are appending to a media daily and if the backup fails today (assumption) then you are atleast left with the backups of previous days since you are appending the previous backups will be present ! but if chose the overwrite option it will overwrite the existing backup file and then will backup the database so if your backup fails due to some reason you are left with no backups for your database.
You can make use of the maintenance plan in sql 2000 to configure backup job and you can specify the number of days to retain the backup file........the backup file will be created with dd/mm/hr format so that it can be deleted by chosing the option "Remove files older than"
http://www.databasejournal.com/features/mssql/article.php/3530486

Friends pls correct me if am wrong

Regards
Deepak
sql

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?
|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.

Please post setup and installation questions in the Setup forum

This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.

Thanks

There was also a feature in Visual Studio 2003 whereby we could view and manage the database present on the server(s) [I think it was called the SQL Enterprise Manager], and also a tool called the Query Analyzer where we could run queries on the databases. Are these also scrapped in VS 2005? What are the alternatives if that is the case?|||

How do I upgrade a SQL Server 2005 trial version with an expired license? I have a real license. If I install a new version, I don't want to lose the reports I created from the old version.

Thank you.

|||

Check out this article. If it's not already too late.

http://support.microsoft.com/?kbid=914158

|||Where is the "Setup Forum"?|||You can find the Setup forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1 |||

Please help me to solve.

How to rectify following error

in Ms Access 2003 in windows20000

(It is working in XP)

"Microsoft Office Access can't open the database because it is missing,or opened exclusively by another user ".

|||When I run my setup.exe and get to the window for "components to install" the Reporting Services option is greyed out! Any ideas? The only thing I can think of is that the person who gave me the software (he isn't a DBA, and don't even ask why I have to go through him!!) gave me the wrong files? or downloaded some partial CD? I'm lost!|||It means you do not have one of the pre-req's, almost certainly IIS.