I used SQL Enterprise manager to attached a previous version of a database under a different name. When I did so the Original was left in the restoring state. The only way I could get the original back to usable state was to then r

Is that possible the database was configured as log shipping?

You cannot usually attach a database that was detached while in STANDBY.

As i understood your post correctly.

You have attached the same database with differnt "database name "of by its earlier version

Now the original was left to restoring state -- is it correct

1) Can you please check the two databases are using two different mdf and ldf files

2) Can you please check why the database is in restoring state, has some one restoring this database

Once the database is restored from the specified backup file and not in recovery mode means, then we need to take that DB as follows to make it operational

RESTORE DATABASE MyDatabase
WITH RECOVERY 

Thanks,

Hello Lee,

Can you please explain the detailed steps you did, because by your Brief post it's not clear for me?

Enterprise Manager, so you are using SQL Server 7.0/2000? Did you really attach the DB and did not run a restore?

What do you mean by attached previous version of database?

Did you have a previous copy of mdf and ldf file of the database? How did you recover the database again to use it?

Placed previous MDF and LDF file into a different directory. From the Attach database GUI in Enterprise manager, I browsed to and selected my file to attach. The GUI shows the database files original name and the two files to attach (LDB AND MDF). In the grid I change the "Attach To" database name, and clock attach. It then attaches the database under the new name, but leaves the original one in "Restoring" state.

So you took the origin database first to offline or did you a detach? Otherwise you can't copy MDF/LDF files, because they are in exclusive access by SQL Server.

VSS Backup of database

VSS Backup of database

You talked alway about "attach", but a backup would mean a restore as a new database. Have you set the "MOVE" option for the restore, otherwise you overwrite your origin database (an that's the reason why it's shown up as in "Restoring state").

Not sure what you know about VSS (Volume Shadow Copy) services and VSS Writers, but SQL is a participator in this technology that Microsoft has been using to make volume backups since XP. When the service is invoked all files that are snapped are available to the backup service which can copy the files to another location package them into a VHD etc. One of these is run every day and I went back to a previous day to obtain the MDF and LDF files, which is where the files came from. Note this is a common practice used in many backup applications and is also supported by Exchange, and SharePoint.&n

Can you help us to try to reproduce what you are seeing? Imagine being "us", and what you would try to do in order to assist. You'd want to see this for yourself. So if you can outline the steps to take for me to reproduce this on my SQL Server and reproduce is, and if I can see the "source" database being in RESTORE then I can figure out what is going on.

If it were the restore dialog, then I can explain what is happening, but you state that it is attach.

I do understand the part about VSS, no worries there. (I.e., you get a consistent file level copy of the database files in the end.)

Also, you say "Enterprise manager", but that tool was replaced by SQL Server Management Studio with SQL Server 2005.

So the more detailed you can be, including versions of the database engine(s), the version and name of the GUI (EM/SSMS) etc, the more likely we can figure out what is happening.

I am surprised by what you are describing, since the attach dialog should have no knowledge of a "source/original" database. So it should no execute any SQL commands on the source db, meaning that it should be impossible for the source db to go into restoring. But if I can repro it, I can trace the SQL submitted by the GUI. Hence the request for a detailed repro...

If you will refer to the original post and change Enterprise Manager to Management Studio...

You would backup your SQL Data using a VSS agent. Then access a version you have backed up and copy to a folder on your SQL server (MDF and LDF)

Open Management Studio and connect to your target server.

Right click "Databases" I the nav panel and select Attach.

Browse to the file you copied above and select it

Change the name of the Target data base to something else.

Attach the database

In my case the original database was left in the "Restoring" state and the Attached database appears under its Target Name.

Microsoft SQL Server Management Studio      12.0.2000.8
Microsoft Analysis Services Client Tools      12.0.2000.8
Microsoft Data Access Components (MDAC)      6.3.9600.17415
Microsoft MSXML      3.0 6.0
Microsoft Internet Explorer      9.11.9600.17498
Microsoft .NET Framework      4.0.30319.34209
Operating System      6.3.9600

Since I don't have the VSS backup ability, I will stop SQL Server and copy the files instead. So, here is what I'm doing. I'm typing below as I do the steps.

SQL 2014, SSMS 2014.

Stop SQL Server. In explorer right-click db files for a database (mdf and ldf), Copy, change file names (original was sqlmaint.mdf and sqlmaint_log.ldf) to sqlmaint1.mdf and sqlmaint1_log.ldf.

Start SQL Server.

Right-click databases, Attach.

Click the "Add button", browse to sqlmaint1.mdf.

Type, in the "Attach As" box sqlmaint1 (the desired name of your database).

And, then in the lower part of this dialog ("sqlmaint" database details), you need to point to your new database files - not the original.

Now, before doing anything else, press the Script button in the "Attach Databases" dialog. Investigate the SQL generated. Here is what I got:

USE [master]
GO
CREATE DATABASE [sqlmaint1] ON 
( FILENAME = N'C:\DemoDatabases\DbFiles\p\sqlmaint1.mdf' ),
( FILENAME = N'C:\DemoDatabases\DbFiles\p\sqlmaint1_log.ldf' )
 FOR ATTACH
GO

As you can see, there is no command executed on the "sqlmaint" database (the original).

I now go back to the GUI and press "OK". the GUI executed the above SQL command. the original database ("sqlmaint") is not in restoring state.

Obvikously you have to replace my database name (sqlmaint) with what you are doing. But if you use above steps and see the same as I see (not restoring), we then can work out whether it is how you drive the GUI or if it is VSS that causes what you are seeing. Or version of SSMS - or even the db engine.

Oh, and btw, it is probably much easier to just use the CREATE DATABASE ... FOR ATTACH command directly instead of trying to investigate the behaviour of the GUI... :-)

 and VSS Writers, but SQL is a participator in this technology

I know, for this we have the SQL Writer Service, where you can run backups with, and the same way you can restore them.

Attaching some other database files will not lead a database to change into "Restoring" mode, only a restore process will do this. Check sys.processes if there is such a restore process.