Re: Manipulating MSDE Files

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/07/04 

Date: Sat, 7 Feb 2004 09:47:44 -0500

Wayne,

You have been around long enough to know you shouldn't delete or copy at the
file level<g>. You really should use Backup and restore but if you want to
do the file level thing you need to use sp_detach_db and sp_attach_db to
ensure they are valid. Just copying the files from the directory does not
ensure they can be reused. Detaching them also cleans up the system tables
so sql server knows they no longer exist etc. But to answer your question
you can try using sp_attach_db to reattach the files to the server. There
is a chance you may get errors if they were not properly detached though. 

-- 
Andrew J. Kelly
SQL Server MVP
"Wayne Wengert" <wayneDONTWANTSPAM@wengert.com> wrote in message
news:OUWDbMY7DHA.2540@TK2MSFTNGP11.phx.gbl...
> I am using MSDE 2K on a WinXP Pro system
>
> I've copied all the MDF and LDF files from c:\Program Files\Microsoft SQL
> Server\MSSQL\Data to a CD/RW for backup. I then restarted MSDE and deleted
> the DBs that I felt were not needed for day-to-day work. I now want to
> re-install one database from the CD/RW. I am not sure how to do this? Can
I
> stop MSDE and just copy the MDF and LDF file for that DB back to the Data
> folder? Do I need to "Create" a new DB with the name of the one I want to
> restore, stop MSDE and overlay the files in the Data folder with the ones
> from the CD/RW?
>
> Any pointers or advice appreciated.
>
>

Relevant Pages

  • Re: Backing up MSDE using Vb.Net
    ... check out the BACKUP DATABASE and BACKUP LOG commands. ... order to restore, you'll use the inverse functions, RESTORE Database. ... down and if you have another machine to back it up to, why not just use MSDE ...
    (microsoft.public.dotnet.general)
  • Installing MSDE - User Security Issues...
    ... replicating a SQL Server 2000 database as an MSDE database in a seperate ... using oSQL to restore the database to an MSDE install on a machine. ... It's done in two steps - installing MSDE and restoring the backup. ...
    (microsoft.public.sqlserver.security)
  • Re: transfer db from msde to msde
    ... need external tools? ... Another option is to do a full backup on the source and then ... > restore on the destination. ... >> is there an easy way to transfer a database from one msde to another ...
    (microsoft.public.sqlserver.msde)
  • Backup/Restore from MSDE 1.0 to MSDE 2000
    ... backup copy of my database in MSDE 1.0 and restore it to MSDE 2000. ...
    (microsoft.public.sqlserver.msde)
  • Re: Bug in Ent Mgr Maintenance Plan Wizard if MSDE vs SQL
    ... So firstly make sure the following backup folder path exists, ... I have many customers using MSDE ... > The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise ...
    (microsoft.public.sqlserver.msde)