Restoring MSDE Databases

I have uninstalled and re-installed an application that uses MSDE on one of
our workstations. Before uninstalling the application I copied the .mdf and
..ldf files to a new location. I now need to restore the databases that are
associated with this application using the .mdf and .ldf files I saved. I
also need to verify the initial configuration (data and log files
names/paths)
I initially tried to detach the database, copy the backup files to the
MSSQL\Data folder and then reattach the database. This worked except that
the database came back in a "read-only" mode. I then opened the properties
of the database and tried to uncheck the "Read-only" option in the
properties. When I tried to save the new settings I received a message that
stated "Device activation error".
Can anyone tell me how I can:
1) Verify the configuration of the database that was installed by the
application I installed.
2) Rebuild this database using the .mdf and .ldf files?
Thanks for any input.
Nancy
Hi Nancy,
Sounds like you're basically on the right track. Is there any chance the
files were copied in from a CD or something and actually are read-only?
If you attach read-only mdf & ldf files, the database comes up in a
read-only mode.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have uninstalled and re-installed an application that uses MSDE on one
of
> our workstations. Before uninstalling the application I copied the .mdf
and
> .ldf files to a new location. I now need to restore the databases that are
> associated with this application using the .mdf and .ldf files I saved. I
> also need to verify the initial configuration (data and log files
> names/paths)
> I initially tried to detach the database, copy the backup files to the
> MSSQL\Data folder and then reattach the database. This worked except that
> the database came back in a "read-only" mode. I then opened the properties
> of the database and tried to uncheck the "Read-only" option in the
> properties. When I tried to save the new settings I received a message
that
> stated "Device activation error".
> Can anyone tell me how I can:
> 1) Verify the configuration of the database that was installed by the
> application I installed.
> 2) Rebuild this database using the .mdf and .ldf files?
> Thanks for any input.
> Nancy
>
|||Hi Greg,
Thanks for the solution. That worked perfectly!!!!
Nancy
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OHp7LaFgEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Nancy,
> Sounds like you're basically on the right track. Is there any chance the
> files were copied in from a CD or something and actually are read-only?
> If you attach read-only mdf & ldf files, the database comes up in a
> read-only mode.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> of
> and
are[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
properties
> that
>

Restoring from unknown .mdf file

My company received a drive with SQL 2000? .mdf and .ldf files. I don't know if they were detached or just copied. I've been unable to reattach the files and get the error bellow when I try sp_attach_db.

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\ocwp.mdf' may be incorrect.

Is there anyway to get this data back short of asking for a new backup?

Thanks,

Tim

The restore is attemping to put the files back in the same location they were located on the original server.

Use the [ WITH MOVE ] option for RESTORE. (From Books Online.)

RESTORE DATABASE { database_name }

FROM <backup_device>

WITH MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]

|||

I was able to get this working by creating a new database with the same name, stopping the SQL instance, replacing the newly created .mdf and .ldf files with the ones with data and restarting the instance. Then I found out they were for 2005 and had to redue to process.

Thanks though.

Tim

Restoring DB

Hi,

I have created a DB on my local machine.
Now I would like to copy this on to another machine.
I stopped the service and then copied both the MDF file and the
log files into the same folder as in Data folder of the second machine.

When I run theStudio express I cannot find the MDF file.It does not show up in the
the DB folder or window.
When I try to create one it says that the DB exists.

What am I missing?

I tried doing a backup and restore but then the DB is not created on the second machine and
hence wont work.

Tnx

How are you looking for the file in Management Studio? Are you using the Attach functionality? If there is more than one SQL Server running on the computer, there will be more than one Data directory, so it's possible that you've put the files in the wrong directory.

Let us know the exact steps you're using to attach the database on the new machine.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||Hi Mike,

>>How are you looking for the file in Management Studio? Are you using the Attach >>functionality?

I stoped the service on the first machine and then went into the folder:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
and copied both the DB and the Log files on to my CD.

I stoped the service on the second machine and then went into the folder.
copied them both into the second machine's :
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

There is only one SQL server installed on both the machines'.

Tnx

|||

Hi,

great you found the button for making fonts real big ! :-)

As Mike said you have to attach the database for before you can see it, use the Sp_attach procedure for this or use the graphical UI (SQL Server Management Studio Express) for that, I don′t know if there is a possibility to use this function for attaching otherwise sp_attachdb is quite easy to code in TSQL, there are samples on the BOL for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de