I'm designing the database and will be using Test1, Test(x) and so on as =
use cases until I get my design refined to the point I can CRUD the =
database from my application with a reasonable expectation that I have a =
decent data model. I assume this is standard fare for neophytes as they =
learn to use SQL Server.

I'm beginning to understand some of the criteria involved thanks to =
comments from Tibor and yourself Mike. Thank you.

<%=3D Clinton Gallagher
[quoted text, click to view]
That's exactly what I was getting at. You were trying to restore a DB =
and write the new files to the exact same location as the old files. If =
you were trying to do it on another server that would have been fine but =
on the same server you've got to make sure you're not going to clobber =
the original DB (hence the WITH MOVE clause, which is the same as =
changing the file locations/names in the GUI tool).

I assumed you had autoclose database option turned on on the Test1 =
database and that would account for why it worked sometimes (because the =
files weren't open) and failed other times with a "file is being used" =
message (although I would have expected SQL Server to do a sanity check =
on the CREATE DATABASE part of the restore to make sure it wasn't =
creating a DB using a filename that was already in use by another DB on =
that same SQL instance). But now you're getting some "tail of the log =
"message I'm not sure what's going on - it's likely to be some odd =
scenario due to the fact that you're trying to get a DB to stomp on =
itself in a rather unnatural way - ouch!

In any case, it's not a SSMS bug, that's by design. Most of the time, =
when you want to restore a database, you want to restore it over the top =
of the original (because some kind of corruption happened). So the GUI =
gets the physical file names from the backup file and uses them in the =
RESTORE statement (unless you change them, as you've discovered). To =
make a copy of the DB from a backup of it you just have to make sure you =
restore it to different physical files (in addition to a different DB =
name, which will also necessitate the WITH REPLACE option too) hence the =
WITH MOVE clause I asked if you were using.

--
mike hodgson
http://sqlnerd.blogspot.com=20



clintonG wrote:=20
OBJECTIVE:
Backup the Test1 database to the file system. Then restore the Test1=20
database from the file system as Test1Copy1.

TRY 1.) Successful: Restore

// General pane
To: Test1Copy1
From Database: Test1

// Options pane
[ ] Overwrite existing
[ ] Preserve replication settings
[ ] Prompt before restoring
[ ] Restrict access to restored

* Leave database ready to use (RESTORE WITH RECOVERY)

TRY 2.) Failed: Restore

// General pane
To: Test1Copy1
From Device (File): Test1.bak

// Options pane
[ ] Overwrite existing
[ ] Preserve replication settings
[ ] Prompt before restoring
[ ] Restrict access to restored

* Leave database ready to use (RESTORE WITH RECOVERY)

TRY 2 ERROR:
System.Data.SqlClient.SqlError: The tail of the log for the database =
"Test1"=20
has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log =
if=20
it contains work you do not want to lose. Use the WITH REPLACE or WITH=20
STOPAT clause of the RESTORE statement to just overwrite the contents of =
the=20
log. (Microsoft.SqlServer.Smo)

TRY 2 NOTE: Restoring a functional copy of Test1 renamed as Test1Copy1 =
is=20
the desired objective. Thus, the (Option pane > Recovery State) option =
must=20
be...

* Leave database ready to use (RESTORE WITH RECOVERY)

During the TRY 2 procedure it was observed on the (Options pane > =
Restore=20
the database files as:) that the name of the files in the (Restore As) =
field=20
were not "Test1Copy1" as entered into the (General pane > To database:) =
as=20
the name of the database destination to restore to. The file names were =
in=20
fact Test1, e.g. the same as the name of the database the backup was =
made=20
from when the file names should have been Test1Copy1 as named in the =
General=20
pane.

The TRY 2 error is resolved by clicking into the (Options pane > Restore =
the=20
database files as: > Restore As) and manually changing file names...

FROM: Test1.mdf TO: Test1Copy1.mdf
FROM: Test1_log.ldf TO: Test1Copy1_log.ldf

This could be a SQL Server Management Studio bug but it is just as =
likely a=20
misunderstanding by a user who is just realizing he not only doesn't =
know=20
his @ss from a hole in the ground he didn't even know there was a hole =
in=20
the ground ;-)

<%=3D Clinton Gallagher

"clintonG" <csgallagher@REMOVETHISTEXTmetromilwaukee.com> wrote in =
message=20
news:O6iZoNIPGHA.1124@TK2MSFTNGP10.phx.gbl...
Thanks, I've got to be out all day with family responsibilities and =
won't=20
be back online until this evening when I'll definitely review my=20
procedures with these comments in mind.

<%=3D Clinton Gallagher

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote =

in message news:e2Y$cNDPGHA.2924@TK2MSFTNGP11.phx.gbl...
... and also check the REPLACE option of the RESTORE command. I =
prefer to=20
drop the database before the restore, unless I know that the current=20
database file layout matches the backup database file layout.

--=20
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


[quoted text, click to view]
Are you using the WITH MOVE option of the RESTORE DATABASE =
statement?
(In the GUI that would be on the options page of the restore dialog box
where it says "Restore the database files as:")

--
*mike hodgson*
http://sqlnerd.blogspot.com



[quoted text, click to view]

While attempting to use SQL Server Manager 2005 to restore Test1 =
to
Test1Copy1 at least 9 times out of 10 I get the following error:

// Restore Test1 to Test1Copy1
System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\Test1.mdf' cannot be overwritten. It is being
used by database 'Test1'. (Microsoft.SqlServer.Smo)

Every once in awhile it restores as I thought it should. I've tried=20
using
the Options tab and the same sporadic results occur. Can somebody=20
explain?

<%=3D Clinton Gallagher