How to attach a db_file with problems?

A database goes "SUSPECT" the client deatach de DB. later it tries to
reatach it but he gets an error. I tried to copy the file to other disk but
I get an error from windows saying that a cyclic error in the disk prevent
the copy. Is there any way to attach a file with problems? just in order to
save at least some of the data?You can create a new database with the same name, using the same data file
and log file names. Then shut down SQL Server and copy your "bad" files
over the top of these new files. When SQL Server starts the database will
once again be suspect. You can then put the database in emergency mode
(status 32768) and recycle SQL Server. At this point you should be able to
at least salvage some of the data, may be all depending on the rason it
went suspect.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
To add on, I had a identical instance where I followed the same procedure as
Rand suggested , after that,
1. The Datbase was able to open in Emergency mode (32768)
2. I was not able to take a backup.
What I did,
1. Create a new database
2. Script out all objects in seperate files
3. Executed the user defined types(UDT) and Table creation script in new
database
4. Run DTS to transfer all data from old to new database ( U can also use
BCP OUT /IN)
5. Executed the Indexe creation script
6. Executed all the other object creation script
After these steps I did a comparison of old and new database and FYI, I lost
few pieces of data which I asked the users to re enter.
Thanks
Hari
MCDBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:M3yLU1E6DHA.2768@.cpmsftngxa07.phx.gbl...

quote:

---

> You can create a new database with the same name, using the same data file
> and log file names. Then shut down SQL Server and copy your "bad" files
> over the top of these new files. When SQL Server starts the database will
> once again be suspect. You can then put the database in emergency mode
> (status 32768) and recycle SQL Server. At this point you should be able to
> at least salvage some of the data, may be all depending on the rason it
> went suspect.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

---

How to attach a db_file with problems?

A database goes "SUSPECT" the client deatach de DB. later it tries to
reatach it but he gets an error. I tried to copy the file to other disk but
I get an error from windows saying that a cyclic error in the disk prevent
the copy. Is there any way to attach a file with problems? just in order to
save at least some of the data?You can create a new database with the same name, using the same data file
and log file names. Then shut down SQL Server and copy your "bad" files
over the top of these new files. When SQL Server starts the database will
once again be suspect. You can then put the database in emergency mode
(status 32768) and recycle SQL Server. At this point you should be able to
at least salvage some of the data, may be all depending on the rason it
went suspect.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
To add on, I had a identical instance where I followed the same procedure as
Rand suggested , after that,
1. The Datbase was able to open in Emergency mode (32768)
2. I was not able to take a backup.
What I did,
1. Create a new database
2. Script out all objects in seperate files
3. Executed the user defined types(UDT) and Table creation script in new
database
4. Run DTS to transfer all data from old to new database ( U can also use
BCP OUT /IN)
5. Executed the Indexe creation script
6. Executed all the other object creation script
After these steps I did a comparison of old and new database and FYI, I lost
few pieces of data which I asked the users to re enter.
Thanks
Hari
MCDBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:M3yLU1E6DHA.2768@.cpmsftngxa07.phx.gbl...
> You can create a new database with the same name, using the same data file
> and log file names. Then shut down SQL Server and copy your "bad" files
> over the top of these new files. When SQL Server starts the database will
> once again be suspect. You can then put the database in emergency mode
> (status 32768) and recycle SQL Server. At this point you should be able to
> at least salvage some of the data, may be all depending on the rason it
> went suspect.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>sql

How to add a new database with a new name by copying an existing datafile?

I want to save a standard SQL Server 2005 Database as a datafile to disk
which I want to add to several other SQL Server at other servers with
another name of the database. What's the procedure to do this? I've been
experimenting with copying the datafiles but SQL Server reponses that the
file names or database name don't match.
regards,
OscarBACKUP the database then use RESTORE WITH MOVE.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Oscar" <oku@.xs4all.nl> wrote in message
news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>I want to save a standard SQL Server 2005 Database as a datafile to disk
>which I want to add to several other SQL Server at other servers with
>another name of the database. What's the procedure to do this? I've been
>experimenting with copying the datafiles but SQL Server reponses that the
>file names or database name don't match.
> regards,
> Oscar
>|||You can also use detach / attach.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> pí¹e v diskusním
pøíspìvku news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>>I want to save a standard SQL Server 2005 Database as a datafile to disk
>>which I want to add to several other SQL Server at other servers with
>>another name of the database. What's the procedure to do this? I've been
>>experimenting with copying the datafiles but SQL Server reponses that the
>>file names or database name don't match.
>> regards,
>> Oscar
>|||> You can also use detach / attach.
Only if it is acceptable for the primary to be offline briefly.|||Hi Aaron,
I want to restore the database within SQL Server Management Studio. I can't
find the 'WITH MOVE' option. It only shows the following settings :
RESTORE WITH RECOVERY
RESTORE WITH NORECOVERY
RESTORE WITH STANDBY
I've tried first to have this done at the same server before I move to
another server :
-made a BACKUP of a resource database
-added a new empty database called TESTCOMPANY
-right click at the database TESTCOMPANY and choose 'RESTORE'
-choose to add from file and select the BACKUP file in the first step
-choose 'OVERWRITE THE EXISTING DATABASE'
Now, in case I set one the 3 listed recovery state, it shows error messages
right in the beginning.
What should I do?
Oscar
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schreef in
bericht news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>>I want to save a standard SQL Server 2005 Database as a datafile to disk
>>which I want to add to several other SQL Server at other servers with
>>another name of the database. What's the procedure to do this? I've been
>>experimenting with copying the datafiles but SQL Server reponses that the
>>file names or database name don't match.
>> regards,
>> Oscar
>|||> I want to restore the database within SQL Server Management Studio.
Sorry, this is going to be like trying to swap out RAM through a USB port --
USB is a great technology but it does not cover all the bases, and the same
is true for SSMS. If the paths on the new machine and old machine are not
identical (and they can't be identical on the same machine), you will likely
have to get your hands dirty and learn/use the RESTORE DATABASE command.
A