When is [With Replace] used

Question

When is [With Replace] used

gyanichinna 45721

SSC Rookie

Points: 40
More actions
October 12, 2009 at 12:50 am

#215434

hi all
Can anyone explain how the option [With Replace] helps during a restoring process.
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Silverfox SSC-Insane Points: 23150 More actions · Answer 1

gyanichinna 45721 (10/12/2009)
hi all
Can anyone explain how the option [With Replace] helps during a restoring process.
Thanks

with replace, is used when you want to overwrite the existing files

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

gyanichinna 45721 SSC Rookie Points: 40 More actions · Answer 2

Thanks for the reply

I have a database with multiple files,

if i restore a file with replace options, is it going to disturb the log.

can u explain with an example where REPLACE can be used

Thanks

homebrew01 SSC Guru Points: 55553 More actions · Answer 3

Small snippet from BOL:

"...Specifies that SQL Server should create the specified database and its related files even if another database already exists with the same name. In such a case, the existing database is deleted. When the REPLACE option is not specified, a safety check occurs, which prevents overwriting a different database by accident...."

I use replace when I want to refresh my test database with production data. I restore my database DB_Production on top of my DB_Test database WITH REPLACE. This wipes out existing DB_Test, and replaces it with DB_Production. Now DB_Test contains a copy of DB_Production at the time the backup was taken.

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 4

What do you mean by disturb the log? When you set up a new database (new or restored), the log starts from zero. Old logs don't matter.

You would use WITH REPLACE in a few places

- You need to overwrite the current database with a backup becuase of some issue (corruption, lost data, etc.)

- You regularly restore the db, such as a dev/test environment.