Backup and Restore Anomaly

SQL Server 2000 SP4 build 2187
Not sure what is happening with SQL Server backup Internals, I am hoping you
can answer.
A scheduled job backups up database named "Wells" to a device which points
to a physical file as step 1. Same schedule job, step 2, then restores to
database WELLSCopy. The WELLSCopy database has always existed. This has been
working for a very long time. Now, its generating the following error.
Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
Database' :
Began Executing 2006-10-19 00:59:34
Msg 3141, Sev 16: The database to be restored was named 'Wells'.
Reissue the statement using the WITH REPLACE option to overwrite the
'WELLScopy'
database. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
Here is the backup command and restore command.
BACKUP DATABASE [Wells] TO Dbackup_WELLS
WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
restore database CHARITYDBCOPY
from dbackup_charitydb
with
move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
I tried to duplicate this issue in our test environment and it restored
successfully every time. I did manage to break it by creating a new database
with a totally different name then try to restore over it and received the
error message.
BOL says it performs a safety check if the REPLACE options is not used. If
so, why has the restore work for a very long time and now decide to break.
Also, why is it allowing me to restore successfully in test without using the
REPLACE option.
I am so confused...
BOL...
When the REPLACE option is not specified, a safety check occurs (which
prevents overwriting a different database by accident). The safety check
ensures that the RESTORE DATABASE statement will not restore the database to
the current server if:
The database named in the RESTORE statement already exists on the current
server, and
The database name is different from the database name recorded in the backup
set.I applogize, I pasted my test restore code. Here is the correct version
RESTORE DATABASE WELLScopy
FROM Dbackup_wells
with
MOVE 'Wells_Data' TO 'e:\data\mssql\data\WELLScopy_Data.MDF',
MOVE 'WELLS_Log' TO 'e:\data\mssql\data\WELLScopy_Log.LDF'
go
"FredG" wrote:
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping you
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has been
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new database
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the backup
> set.
>|||A guess is that the source database has grown, so the backup need say 10GB for one of the database
files, but the existing database file only has, say, 8GB for that file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> SQL Server 2000 SP4 build 2187
> Not sure what is happening with SQL Server backup Internals, I am hoping you
> can answer.
> A scheduled job backups up database named "Wells" to a device which points
> to a physical file as step 1. Same schedule job, step 2, then restores to
> database WELLSCopy. The WELLSCopy database has always existed. This has been
> working for a very long time. Now, its generating the following error.
> Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> Database' :
> Began Executing 2006-10-19 00:59:34
> Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> Reissue the statement using the WITH REPLACE option to overwrite the
> 'WELLScopy'
> database. [SQLSTATE 42000]
> Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
>
> Here is the backup command and restore command.
> BACKUP DATABASE [Wells] TO Dbackup_WELLS
> WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
>
> restore database CHARITYDBCOPY
> from dbackup_charitydb
> with
> move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
>
> I tried to duplicate this issue in our test environment and it restored
> successfully every time. I did manage to break it by creating a new database
> with a totally different name then try to restore over it and received the
> error message.
> BOL says it performs a safety check if the REPLACE options is not used. If
> so, why has the restore work for a very long time and now decide to break.
> Also, why is it allowing me to restore successfully in test without using the
> REPLACE option.
> I am so confused...
> BOL...
> When the REPLACE option is not specified, a safety check occurs (which
> prevents overwriting a different database by accident). The safety check
> ensures that the RESTORE DATABASE statement will not restore the database to
> the current server if:
> The database named in the RESTORE statement already exists on the current
> server, and
> The database name is different from the database name recorded in the backup
> set.
>|||Hi Tibor,
Thanks for the repsonse. We finally found the actual problem. Someone
restored the copy data outside of the normal job. Once we dropped and
recreated the job functioned as normal. Very strange behavior I must add.
"Tibor Karaszi" wrote:
> A guess is that the source database has grown, so the backup need say 10GB for one of the database
> files, but the existing database file only has, say, 8GB for that file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "FredG" <FredG@.discussions.microsoft.com> wrote in message
> news:1D2AF5E1-9337-4F55-84EE-B4711171D7C9@.microsoft.com...
> > SQL Server 2000 SP4 build 2187
> >
> > Not sure what is happening with SQL Server backup Internals, I am hoping you
> > can answer.
> >
> > A scheduled job backups up database named "Wells" to a device which points
> > to a physical file as step 1. Same schedule job, step 2, then restores to
> > database WELLSCopy. The WELLSCopy database has always existed. This has been
> > working for a very long time. Now, its generating the following error.
> >
> > Job '0405 Backup and WELLSCopy Restore' : Step 3, 'Restore Wells Copy
> > Database' :
> > Began Executing 2006-10-19 00:59:34
> > Msg 3141, Sev 16: The database to be restored was named 'Wells'.
> > Reissue the statement using the WITH REPLACE option to overwrite the
> > 'WELLScopy'
> > database. [SQLSTATE 42000]
> > Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
> >
> >
> > Here is the backup command and restore command.
> > BACKUP DATABASE [Wells] TO Dbackup_WELLS
> > WITH INIT, NOUNLOAD, NAME = N'Wells', SKIP, STATS=10, NOFORMAT
> >
> >
> > restore database CHARITYDBCOPY
> > from dbackup_charitydb
> > with
> > move 'CHARITYDB_Data' to 'o:\Data\CHARITYDBCOPY_Data.MDF',
> > move 'CHARITYDB_Log' to 'o:\Data\CHARITYDBCOPY_Log.LDF'
> >
> >
> > I tried to duplicate this issue in our test environment and it restored
> > successfully every time. I did manage to break it by creating a new database
> > with a totally different name then try to restore over it and received the
> > error message.
> >
> > BOL says it performs a safety check if the REPLACE options is not used. If
> > so, why has the restore work for a very long time and now decide to break.
> > Also, why is it allowing me to restore successfully in test without using the
> > REPLACE option.
> >
> > I am so confused...
> >
> > BOL...
> > When the REPLACE option is not specified, a safety check occurs (which
> > prevents overwriting a different database by accident). The safety check
> > ensures that the RESTORE DATABASE statement will not restore the database to
> > the current server if:
> > The database named in the RESTORE statement already exists on the current
> > server, and
> > The database name is different from the database name recorded in the backup
> > set.
> >
>