db restore question

Hi all, is it possible to restore a database to another server with only the
.mdf & the .ldf files? Unfortunately I dont have a backup file, only the
data files itself. Also another thing is the data files are from sql2k and
the new server is sql2k sp3a. Am i SOL?
TIA!If you are lucky, or you explicitly detached then first, you can use sp_attach_db to attach the
database files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RP" <rp@.nospam.com> wrote in message news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>|||You can try to use sp_attach_db to attach the files but this might not work
if the database wasn't cleanly detached using sp_detach_db. See the Books
Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"RP" <rp@.nospam.com> wrote in message
news:ue9YVIXqEHA.708@.tk2msftngp13.phx.gbl...
> Hi all, is it possible to restore a database to another server with only
> the
> .mdf & the .ldf files? Unfortunately I dont have a backup file, only the
> data files itself. Also another thing is the data files are from sql2k and
> the new server is sql2k sp3a. Am i SOL?
> TIA!
>|||Hi!
As for your last problem. That will be no problem.
As for attach/detach procedure:
You will get the database with all data but you will get no users that reside in master. You have to create them and mapp them using following script:
--Run this in the faulty database
--Username is the databaseusername
--Loginname is the user in Security
sp_change_users_login 'Update_one', '<username>','<Loginna
me>'
If you database wasn=B4t cleanly closed you can not attach it directly. But that doesn=B4t make the database lost forever.
Do the following steps and you are homefree:
1. Reconfigure server to allow ad-hoc updates.
2. Create the database and set the physical names as those in the one you are restoring. 3. Close SQL-server and delete the .ldf file
4. Start SQL-server, the database is now in suspect mode.
5. Set the database to emergencymode
6. Activate traceflag 3604
7. Run DBCC Rebuild_log
8. Reset the database to normal operations.
9. Run DBCC Checkdb and correct any errornes indexes.
If you need further help drop me a mail.
Jesper Kr=E5khede, Capgemini
>--Original Message--
>Hi all, is it possible to restore a database to another server with only the
>..mdf & the .ldf files? Unfortunately I dont have a backup file, only the
>data files itself. Also another thing is the data files are from sql2k and
>the new server is sql2k sp3a. Am i SOL?
>TIA!
>
>.
>|||Note that rebuilding the log will compromise both logical and physical
database integrity. If RP can't successfully attach the database and the
data are important, I suggest he or she contact Microsoft PSS.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jesper Kråkhede Capgemini" <Jesper.krakhede@.capgemini.com> wrote in message
news:2fde01c4a979$588ed870$a401280a@.phx.gbl...
Hi!
As for your last problem. That will be no problem.
As for attach/detach procedure:
You will get the database with all data but you will get
no users that reside in master. You have to create them
and mapp them using following script:
--Run this in the faulty database
--Username is the databaseusername
--Loginname is the user in Security
sp_change_users_login 'Update_one', '<username>','<Loginna
me>'
If you database wasn´t cleanly closed you can not attach
it directly. But that doesn´t make the database lost
forever.
Do the following steps and you are homefree:
1. Reconfigure server to allow ad-hoc updates.
2. Create the database and set the physical names as
those in the one you are restoring.
3. Close SQL-server and delete the .ldf file
4. Start SQL-server, the database is now in suspect mode.
5. Set the database to emergencymode
6. Activate traceflag 3604
7. Run DBCC Rebuild_log
8. Reset the database to normal operations.
9. Run DBCC Checkdb and correct any errornes indexes.
If you need further help drop me a mail.
Jesper Kråkhede, Capgemini
>--Original Message--
>Hi all, is it possible to restore a database to another
server with only the
>..mdf & the .ldf files? Unfortunately I dont have a
backup file, only the
>data files itself. Also another thing is the data files
are from sql2k and
>the new server is sql2k sp3a. Am i SOL?
>TIA!
>
>.
>|||Thats correct. I did that once and this was the solution they gave me. Worked
as a charm but running DBCC CheckDB was needed afterwards.
J
"Dan Guzman" wrote:
> Note that rebuilding the log will compromise both logical and physical
> database integrity. If RP can't successfully attach the database and the
> data are important, I suggest he or she contact Microsoft PSS.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jesper Kråkhede Capgemini" <Jesper.krakhede@.capgemini.com> wrote in message
> news:2fde01c4a979$588ed870$a401280a@.phx.gbl...
> Hi!
> As for your last problem. That will be no problem.
> As for attach/detach procedure:
> You will get the database with all data but you will get
> no users that reside in master. You have to create them
> and mapp them using following script:
> --Run this in the faulty database
> --Username is the databaseusername
> --Loginname is the user in Security
> sp_change_users_login 'Update_one', '<username>','<Loginna
> me>'
> If you database wasn´t cleanly closed you can not attach
> it directly. But that doesn´t make the database lost
> forever.
> Do the following steps and you are homefree:
> 1. Reconfigure server to allow ad-hoc updates.
> 2. Create the database and set the physical names as
> those in the one you are restoring.
> 3. Close SQL-server and delete the .ldf file
> 4. Start SQL-server, the database is now in suspect mode.
> 5. Set the database to emergencymode
> 6. Activate traceflag 3604
> 7. Run DBCC Rebuild_log
> 8. Reset the database to normal operations.
> 9. Run DBCC Checkdb and correct any errornes indexes.
> If you need further help drop me a mail.
> Jesper Kråkhede, Capgemini
>
> >--Original Message--
> >Hi all, is it possible to restore a database to another
> server with only the
> >..mdf & the .ldf files? Unfortunately I dont have a
> backup file, only the
> >data files itself. Also another thing is the data files
> are from sql2k and
> >the new server is sql2k sp3a. Am i SOL?
> >
> >TIA!
> >
> >
> >.
> >
>
>