restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql

restoring without log

Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>

Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLSe...=2706
51
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270651
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Restoring SQL Database

I have the actual .mdf and .ldf files for an SQL database,
not sQL backup files. I need to get this database to a
new SQL server. The server that the previous database was
on was destroyed in a fire, so I can't produce a bakup set
to restore. I have read about attaching a database, and
after running the SP which says it completed successfully,
the database does not appear in the new server. Help
please! Thanks!Matt
Example from the BOL. (NOTE it uses sp_attach_single_files strored
procedure)
For details please refer to BOL.
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
"Matt" <matt_and_ann@.hotmail.com> wrote in message
news:020801c34e5e$13b22440$a401280a@.phx.gbl...
> I have the actual .mdf and .ldf files for an SQL database,
> not sQL backup files. I need to get this database to a
> new SQL server. The server that the previous database was
> on was destroyed in a fire, so I can't produce a bakup set
> to restore. I have read about attaching a database, and
> after running the SP which says it completed successfully,
> the database does not appear in the new server. Help
> please! Thanks!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring only .MDF file

Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I res
tore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.Katrhik
Regardless of recovery model of you SQL Server you will have log file.
if you detached your database and removed the log file so use
This example detaches pubs and then attaches one file from pubs to the
current server.
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf'Note : you will have log file created by sql
server with (if i remmember well) size of 2MB.
"Karthik" <anonymous@.discussions.microsoft.com> wrote in message
news:AA2705B7-F22A-4BB8-B9CD-6064D554721B@.microsoft.com...

quote:

---

> Hi,
> I have a backup of a database which is around 1.5 GB in size. Whenever I

---

restore the database via EM I get both the MDF and the LDF file.

quote:

---

> Because of this I need a tremendous amount of space in the hard disk.
> Is there anyway, wherein I can only restore the MDF file?
> Regards,
> Karthik.

---

|||Try simply attaching the MDF and a blank LDF files
Glen Victor
-- Karthik wrote: --
Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I res
tore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.sql

Restoring only .MDF file

Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I restore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.Katrhik
Regardless of recovery model of you SQL Server you will have log file.
if you detached your database and removed the log file so use
This example detaches pubs and then attaches one file from pubs to the
current server.
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf'Note : you will have log file created by sql
server with (if i remmember well) size of 2MB.
"Karthik" <anonymous@.discussions.microsoft.com> wrote in message
news:AA2705B7-F22A-4BB8-B9CD-6064D554721B@.microsoft.com...
> Hi,
> I have a backup of a database which is around 1.5 GB in size. Whenever I
restore the database via EM I get both the MDF and the LDF file.
> Because of this I need a tremendous amount of space in the hard disk.
> Is there anyway, wherein I can only restore the MDF file?
> Regards,
> Karthik.|||Try simply attaching the MDF and a blank LDF files
Glen Victor
-- Karthik wrote: --
Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I restore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.

restoring old mdf file

I need to restore a Sql2000 database from an .mdf file from last July. Can
people tell me if the following procedure is correct, or am I missing
something? :
1. restore the .mdf file from backup to temp location (done)
2. backup existing database (in case I need to restore this one)
3. delete existing database (to cleanup metadata in master)
4. shutdown sqlserver
5. replace existing .mdf file with old one
6. startup sqlserver
7. attach database to .mdf -- how do I do this?
Thanks for any assistance with this.
-Frank Brown
Seattle Fire Dept
http://www.inwa.net/~frog/take a look at the sp_attach_db procedure, in BOL.
Alex Ivascu
"frank brown" <someone@.somewhere.net> wrote in message
news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> I need to restore a Sql2000 database from an .mdf file from last July.
Can
> people tell me if the following procedure is correct, or am I missing
> something? :
> 1. restore the .mdf file from backup to temp location (done)
> 2. backup existing database (in case I need to restore this one)
> 3. delete existing database (to cleanup metadata in master)
> 4. shutdown sqlserver
> 5. replace existing .mdf file with old one
> 6. startup sqlserver
> 7. attach database to .mdf -- how do I do this?
> Thanks for any assistance with this.
> -Frank Brown
> Seattle Fire Dept
> http://www.inwa.net/~frog/
>|||Hi ,
This activity do not require a SQL server shutdown. Please use the below
commands to perform:
1. drop database databasename
2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
3. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Now your old database will be ready to use.
Incase if you need the old and new database in server then u have to use
Move option along with
sp_attach_db command.
Thanks
Hari
MCDBA
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> take a look at the sp_attach_db procedure, in BOL.
> Alex Ivascu
>
> "frank brown" <someone@.somewhere.net> wrote in message
> news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > I need to restore a Sql2000 database from an .mdf file from last July.
> Can
> > people tell me if the following procedure is correct, or am I missing
> > something? :
> >
> > 1. restore the .mdf file from backup to temp location (done)
> > 2. backup existing database (in case I need to restore this one)
> > 3. delete existing database (to cleanup metadata in master)
> > 4. shutdown sqlserver
> > 5. replace existing .mdf file with old one
> > 6. startup sqlserver
> > 7. attach database to .mdf -- how do I do this?
> >
> > Thanks for any assistance with this.
> >
> > -Frank Brown
> > Seattle Fire Dept
> > http://www.inwa.net/~frog/
> >
> >
>|||Huh? If you drop the database, there are no more database file...
Also, sp_attach_db is only guaranteed to work if you first detach the database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> This activity do not require a SQL server shutdown. Please use the below
> commands to perform:
> 1. drop database databasename
> 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
> 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> @.filename1 = N'c:\mssql\data\dbname.mdf',
> @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> Now your old database will be ready to use.
> Incase if you need the old and new database in server then u have to use
> Move option along with
> sp_attach_db command.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > take a look at the sp_attach_db procedure, in BOL.
> >
> > Alex Ivascu
> >
> >
> > "frank brown" <someone@.somewhere.net> wrote in message
> > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > I need to restore a Sql2000 database from an .mdf file from last July.
> > Can
> > > people tell me if the following procedure is correct, or am I missing
> > > something? :
> > >
> > > 1. restore the .mdf file from backup to temp location (done)
> > > 2. backup existing database (in case I need to restore this one)
> > > 3. delete existing database (to cleanup metadata in master)
> > > 4. shutdown sqlserver
> > > 5. replace existing .mdf file with old one
> > > 6. startup sqlserver
> > > 7. attach database to .mdf -- how do I do this?
> > >
> > > Thanks for any assistance with this.
> > >
> > > -Frank Brown
> > > Seattle Fire Dept
> > > http://www.inwa.net/~frog/
> > >
> > >
> >
> >
>|||Hi,
Frank's mail says that he need to drop the existing database and load the
old database. That is the reason I mentioned the "drop database" command
initially.
I do agree with you , some times the SP_attachdb wont work incase if we are
not using SP_detachdb.
Frank,
What you can do is perform the below steps to put you in safer
side.
1. Perform a backup of your existing database and keep it in a safe folder.
2. drop database databasename
3. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
4. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e0RKXhfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Huh? If you drop the database, there are no more database file...
> Also, sp_attach_db is only guaranteed to work if you first detach the
database.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> >
> > This activity do not require a SQL server shutdown. Please use the below
> > commands to perform:
> >
> > 1. drop database databasename
> > 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any
directory)
> > 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> > @.filename1 = N'c:\mssql\data\dbname.mdf',
> > @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> >
> > Now your old database will be ready to use.
> >
> > Incase if you need the old and new database in server then u have to use
> > Move option along with
> > sp_attach_db command.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> > news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > > take a look at the sp_attach_db procedure, in BOL.
> > >
> > > Alex Ivascu
> > >
> > >
> > > "frank brown" <someone@.somewhere.net> wrote in message
> > > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > > I need to restore a Sql2000 database from an .mdf file from last
July.
> > > Can
> > > > people tell me if the following procedure is correct, or am I
missing
> > > > something? :
> > > >
> > > > 1. restore the .mdf file from backup to temp location (done)
> > > > 2. backup existing database (in case I need to restore this one)
> > > > 3. delete existing database (to cleanup metadata in master)
> > > > 4. shutdown sqlserver
> > > > 5. replace existing .mdf file with old one
> > > > 6. startup sqlserver
> > > > 7. attach database to .mdf -- how do I do this?
> > > >
> > > > Thanks for any assistance with this.
> > > >
> > > > -Frank Brown
> > > > Seattle Fire Dept
> > > > http://www.inwa.net/~frog/
> > > >
> > > >
> > >
> > >
> >
> >
>

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 from MDF and LDF

Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JKDid you try attaching them?
http://www.aspfaq.com/
(Reverse address to reply.)
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF
and LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\data\name_Log.LDF' may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK|||Can you somehow add a D: drive, place the files in the right path and try
attaching again.
Or else, find another SQL Server with D: drive and try attaching these
databases to that server.
Note that, if you haven't detached these databases previously, you may not
be able to attach them successfully.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK|||Yes I tried using Enterprise Manager and the Attach Database as well as T-SQ
L sp_attach_db
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching them?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> and LDF files for SQL Server Database.
> 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
>
>|||I tried this too then it gives error:
An error occurred while processing the log for database 'name'
Connection broken
"Narayana Vyas Kondreddi" wrote:

> Can you somehow add a D: drive, place the files in the right path and try
> attaching again.
> Or else, find another SQL Server with D: drive and try attaching these
> databases to that server.
> Note that, if you haven't detached these databases previously, you may not
> be able to attach them successfully.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF a
nd
> LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LD
F'
> may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
>
>|||Are you at all familiar with DBCC REBUILD_LOG ? It saved my bacon once
with minimal data loss.
In article <1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com>,
JK@.discussions.microsoft.com said...
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
>|||Hi JK,
Copy the original MDF and LDF to a safe location and try attaching the
database only with MDF file. This will not work if you have more
than 1 MDF and LDF files.
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'c:\MSSQL\Data\pubs.mdf'
If the above fail then try:- ( If you have a backup for the database then
use that file to restore)
1. Start database in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode. After this database will
be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
Thanks
Hari
MCDBA
"JK" <JK@.discussions.microsoft.com> wrote in message
news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...[vbcol=seagreen]
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
try[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
'D:\MSSQL\data\name_Log.LDF'[vbcol=seagreen]|||Why not do the following:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'BADDBNAME'
go
dbcc rebuild_log('BADDBNAME','C:\NewLogFile.ldf')
go
update sysdatabases
set status = 0
where name = 'BADDBNAME'
go
sp_configure 'allow updates',0
reconfigure with override
Then it will create a new log file and it should be usable.
Just know that rebuild_log is not supported by Microsoft although I have
had it recommeneded to me by MSPSS in the past to solve corruption issues.
They make you sign a waiver before actually recommending it.
In article <udckLvtaEHA.3596@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com said...
[vbcol=seagreen]
> Copy the original MDF and LDF to a safe location and try attaching the
> database only with MDF file. This will not work if you have more
> than 1 MDF and LDF files.
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'c:\MSSQL\Data\pubs.mdf'
>
> If the above fail then try:- ( If you have a backup for the database then
> use that file to restore)
>
> 1. Start database in emergency mode
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data.
> To get your data, use this script:
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
> You might be able to use bulk copy program (bcp), simple SELECT commands,
or
> use DTS to extract
> your data while the database is in emergency mode. After this database wil
l
> be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data
> Thanks
> Hari
> MCDBA
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...
> try
> not
> and
> 'D:\MSSQL\data\name_Log.LDF'

Restoring from MDF and LDF

Hi,
Our hard disk parition failed and the only thing we can recover were MDF and LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK
Did you try attaching them?
http://www.aspfaq.com/
(Reverse address to reply.)
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF
and LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
'D:\MSSQL\data\name_Log.LDF' may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
|||Can you somehow add a D: drive, place the files in the right path and try
attaching again.
Or else, find another SQL Server with D: drive and try attaching these
databases to that server.
Note that, if you haven't detached these databases previously, you may not
be able to attach them successfully.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"JK" <JK@.discussions.microsoft.com> wrote in message
news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
Hi,
Our hard disk parition failed and the only thing we can recover were MDF and
LDF files for SQL Server Database.
How do we recover the database from these files?
When we try using sp_attach_db we get this error :
Could not open new database 'name'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
may be incorrect.
We do not have D:\ drive any more everything is in C:\ now.
Any help would be appreciated...
Thanks
JK
|||Yes I tried using Enterprise Manager and the Attach Database as well as T-SQL sp_attach_db
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching them?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> and LDF files for SQL Server Database.
> 'D:\MSSQL\data\name_Log.LDF' may be incorrect.
>
>
|||I tried this too then it gives error:
An error occurred while processing the log for database 'name'
Connection broken
"Narayana Vyas Kondreddi" wrote:

> Can you somehow add a D: drive, place the files in the right path and try
> attaching again.
> Or else, find another SQL Server with D: drive and try attaching these
> databases to that server.
> Note that, if you haven't detached these databases previously, you may not
> be able to attach them successfully.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:C5B5AEC5-94AC-4F62-9592-0646D625F151@.microsoft.com...
> Hi,
> Our hard disk parition failed and the only thing we can recover were MDF and
> LDF files for SQL Server Database.
> How do we recover the database from these files?
> When we try using sp_attach_db we get this error :
> Could not open new database 'name'. CREATE DATABASE is aborted.
> Device activation error. The physical file name 'D:\MSSQL\data\name_Log.LDF'
> may be incorrect.
> We do not have D:\ drive any more everything is in C:\ now.
> Any help would be appreciated...
> Thanks
> JK
>
>
|||Are you at all familiar with DBCC REBUILD_LOG ? It saved my bacon once
with minimal data loss.
In article <1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com>,
JK@.discussions.microsoft.com said...
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
>
|||Hi JK,
Copy the original MDF and LDF to a safe location and try attaching the
database only with MDF file. This will not work if you have more
than 1 MDF and LDF files.
EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
'c:\MSSQL\Data\pubs.mdf'
If the above fail then try:- ( If you have a backup for the database then
use that file to restore)
1. Start database in emergency mode
Setting the database status to emergency mode tells SQL Server to skip
automatic recovery and lets you access the data.
To get your data, use this script:
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
You might be able to use bulk copy program (bcp), simple SELECT commands, or
use DTS to extract
your data while the database is in emergency mode. After this database will
be usable with out transaction log. AFter this
create a new database and use DTS to transfer objects and data
Thanks
Hari
MCDBA
"JK" <JK@.discussions.microsoft.com> wrote in message
news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...[vbcol=seagreen]
> I tried this too then it gives error:
> An error occurred while processing the log for database 'name'
> Connection broken
> "Narayana Vyas Kondreddi" wrote:
try[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
'D:\MSSQL\data\name_Log.LDF'[vbcol=seagreen]
|||Why not do the following:
sp_configure 'allow updates',1
reconfigure with override
go
update sysdatabases
set status = 32768
where name = 'BADDBNAME'
go
dbcc rebuild_log('BADDBNAME','C:\NewLogFile.ldf')
go
update sysdatabases
set status = 0
where name = 'BADDBNAME'
go
sp_configure 'allow updates',0
reconfigure with override
Then it will create a new log file and it should be usable.
Just know that rebuild_log is not supported by Microsoft although I have
had it recommeneded to me by MSPSS in the past to solve corruption issues.
They make you sign a waiver before actually recommending it.
In article <udckLvtaEHA.3596@.tk2msftngp13.phx.gbl>,
hari_prasad_k@.hotmail.com said...
[vbcol=seagreen]
> Copy the original MDF and LDF to a safe location and try attaching the
> database only with MDF file. This will not work if you have more
> than 1 MDF and LDF files.
> EXEC sp_attach_single_file_db @.dbname = 'pubs', @.physname =
> 'c:\MSSQL\Data\pubs.mdf'
>
> If the above fail then try:- ( If you have a backup for the database then
> use that file to restore)
>
> 1. Start database in emergency mode
> Setting the database status to emergency mode tells SQL Server to skip
> automatic recovery and lets you access the data.
> To get your data, use this script:
> Sp_configure "allow updates", 1
> go
> Reconfigure with override
> GO
> Update sysdatabases set status = 32768 where name = "BadDbName"
> go
> Sp_configure "allow updates", 0
> go
> Reconfigure with override
> GO
> You might be able to use bulk copy program (bcp), simple SELECT commands, or
> use DTS to extract
> your data while the database is in emergency mode. After this database will
> be usable with out transaction log. AFter this
> create a new database and use DTS to transfer objects and data
> Thanks
> Hari
> MCDBA
> "JK" <JK@.discussions.microsoft.com> wrote in message
> news:1A30B41D-8840-4837-BE9C-588A3373C87B@.microsoft.com...
> try
> not
> and
> 'D:\MSSQL\data\name_Log.LDF'

Restoring from Backup without logs

We have received a backup file that is 6G in size. The log file is
about 74G while the MDF is about 5 G when we restored. Is there a way
to run a SQL script to restore the .BAK file without restoring the log
file as we do not have enough space on the server.

PS. We are running Sql2K5.

Thanks!GM,

See my recent entry under "Restore database with no log". From what I
just went through, I think that your first and best option is to
tell the database owner/administrator to do a backup of the
transaction log, shrink the file and then send you
another backup. If you can't do that, you'll have to find a server
with enough disk space (NTFS, not FAT) to
on which to restore your backup so that you can shrink the logs
yourself. See Erland's advice to me on this matter.

http://groups.google.com/group/comp...0626d528f12287c
Bill E.

GM wrote:

Quote:

Originally Posted by

We have received a backup file that is 6G in size. The log file is
about 74G while the MDF is about 5 G when we restored. Is there a way
to run a SQL script to restore the .BAK file without restoring the log
file as we do not have enough space on the server.
>
PS. We are running Sql2K5.
>
Thanks!

Restoring from an copy of the offline database?

I have a question considering restores. If I take my database offline
the backup system will backup the .mdf and .ldf files making up the
database. Can these files be used to restore a SQL 2000 database.
The reason I ask is that we don't have enough space to create a full
back up and we cannot write directly to a tape device. I have no
problem detaching the database, letting that backup, then reattaching
it. However, if I am not here I'd rather leave instructions for my
boss to just take the database offline when he leaves for the weekend
and bring it back online Monday morning.This may work, but it is not guaranteed.
I would backup to a USB drive or a UNC share across the network. If you
don't have the space to handle a backup copy and a restore copy of the
database, you really shouldn't be running SQL Server.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Randy K" <wawork@.hotmail.com> wrote in message
news:44491b1e.70492156@.msnews.microsoft.com...
>I have a question considering restores. If I take my database offline
> the backup system will backup the .mdf and .ldf files making up the
> database. Can these files be used to restore a SQL 2000 database.
> The reason I ask is that we don't have enough space to create a full
> back up and we cannot write directly to a tape device. I have no
> problem detaching the database, letting that backup, then reattaching
> it. However, if I am not here I'd rather leave instructions for my
> boss to just take the database offline when he leaves for the weekend
> and bring it back online Monday morning.
>|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> This may work, but it is not guaranteed.
> I would backup to a USB drive or a UNC share across the network. If you
> don't have the space to handle a backup copy and a restore copy of the
> database, you really shouldn't be running SQL Server.
We use the UNC share method all the time and in fact just upgraded to a Snap
4200 server.
Works very nicely for us. Not the fastest solution out there, but almost 3
times faster than our Snap Server 4100 was, so I'm happy.
(100+ gig in about 3 hours).

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Randy K" <wawork@.hotmail.com> wrote in message
> news:44491b1e.70492156@.msnews.microsoft.com...
>|||Personally, I use the UNC share method for t-log backups and recent full
backups. I use tape drives for longer term backups and archiving. My NT
admins like me because I use the tape drives during daylight hours and the
network at night, both of which are low activity times for the respective
resources.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:elfo4aaZGHA.3652@.TK2MSFTNGP03.phx.gbl...
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> We use the UNC share method all the time and in fact just upgraded to a
> Snap
> 4200 server.
> Works very nicely for us. Not the fastest solution out there, but almost
> 3
> times faster than our Snap Server 4100 was, so I'm happy.
> (100+ gig in about 3 hours).
>
>|||Thank you for your suggestions!
Another question we have concerns the maximum size for the physical
database files when building VLDBs. This database will start out
about 2TB growing to 4 over the next year and a half. I haven't
been able to find anything on this subject. With two 1TB LUNs is
250GB too large for individual database files?
On Sun, 23 Apr 2006 23:41:08 -0400, "Geoff N. Hiten"
<SQLCraftsman@.gmail.com> wrote:

>Personally, I use the UNC share method for t-log backups and recent full
>backups. I use tape drives for longer term backups and archiving. My NT
>admins like me because I use the tape drives during daylight hours and the
>network at night, both of which are low activity times for the respective
>resources.
>--
>Geoff N. Hiten
>Senior Database Administrator
>Microsoft SQL Server MVP
>
>"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
>news:elfo4aaZGHA.3652@.TK2MSFTNGP03.phx.gbl...
>

Restoring from an copy of the offline database?

I have a question considering restores. If I take my database offline
the backup system will backup the .mdf and .ldf files making up the
database. Can these files be used to restore a SQL 2000 database.
The reason I ask is that we don't have enough space to create a full
back up and we cannot write directly to a tape device. I have no
problem detaching the database, letting that backup, then reattaching
it. However, if I am not here I'd rather leave instructions for my
boss to just take the database offline when he leaves for the weekend
and bring it back online Monday morning.This may work, but it is not guaranteed.
I would backup to a USB drive or a UNC share across the network. If you
don't have the space to handle a backup copy and a restore copy of the
database, you really shouldn't be running SQL Server.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Randy K" <wawork@.hotmail.com> wrote in message
news:44491b1e.70492156@.msnews.microsoft.com...
>I have a question considering restores. If I take my database offline
> the backup system will backup the .mdf and .ldf files making up the
> database. Can these files be used to restore a SQL 2000 database.
> The reason I ask is that we don't have enough space to create a full
> back up and we cannot write directly to a tape device. I have no
> problem detaching the database, letting that backup, then reattaching
> it. However, if I am not here I'd rather leave instructions for my
> boss to just take the database offline when he leaves for the weekend
> and bring it back online Monday morning.
>|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> This may work, but it is not guaranteed.
> I would backup to a USB drive or a UNC share across the network. If you
> don't have the space to handle a backup copy and a restore copy of the
> database, you really shouldn't be running SQL Server.
We use the UNC share method all the time and in fact just upgraded to a Snap
4200 server.
Works very nicely for us. Not the fastest solution out there, but almost 3
times faster than our Snap Server 4100 was, so I'm happy.
(100+ gig in about 3 hours).
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Randy K" <wawork@.hotmail.com> wrote in message
> news:44491b1e.70492156@.msnews.microsoft.com...
> >I have a question considering restores. If I take my database offline
> > the backup system will backup the .mdf and .ldf files making up the
> > database. Can these files be used to restore a SQL 2000 database.
> >
> > The reason I ask is that we don't have enough space to create a full
> > back up and we cannot write directly to a tape device. I have no
> > problem detaching the database, letting that backup, then reattaching
> > it. However, if I am not here I'd rather leave instructions for my
> > boss to just take the database offline when he leaves for the weekend
> > and bring it back online Monday morning.
> >
> >
>|||Personally, I use the UNC share method for t-log backups and recent full
backups. I use tape drives for longer term backups and archiving. My NT
admins like me because I use the tape drives during daylight hours and the
network at night, both of which are low activity times for the respective
resources.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:elfo4aaZGHA.3652@.TK2MSFTNGP03.phx.gbl...
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
>> This may work, but it is not guaranteed.
>> I would backup to a USB drive or a UNC share across the network. If you
>> don't have the space to handle a backup copy and a restore copy of the
>> database, you really shouldn't be running SQL Server.
> We use the UNC share method all the time and in fact just upgraded to a
> Snap
> 4200 server.
> Works very nicely for us. Not the fastest solution out there, but almost
> 3
> times faster than our Snap Server 4100 was, so I'm happy.
> (100+ gig in about 3 hours).
>
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?id=555128
>>
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Randy K" <wawork@.hotmail.com> wrote in message
>> news:44491b1e.70492156@.msnews.microsoft.com...
>> >I have a question considering restores. If I take my database offline
>> > the backup system will backup the .mdf and .ldf files making up the
>> > database. Can these files be used to restore a SQL 2000 database.
>> >
>> > The reason I ask is that we don't have enough space to create a full
>> > back up and we cannot write directly to a tape device. I have no
>> > problem detaching the database, letting that backup, then reattaching
>> > it. However, if I am not here I'd rather leave instructions for my
>> > boss to just take the database offline when he leaves for the weekend
>> > and bring it back online Monday morning.
>> >
>> >
>>
>|||Thank you for your suggestions!
Another question we have concerns the maximum size for the physical
database files when building VLDBs. This database will start out
about 2TB growing to 4 over the next year and a half. I haven't
been able to find anything on this subject. With two 1TB LUNs is
250GB too large for individual database files?
On Sun, 23 Apr 2006 23:41:08 -0400, "Geoff N. Hiten"
<SQLCraftsman@.gmail.com> wrote:
>Personally, I use the UNC share method for t-log backups and recent full
>backups. I use tape drives for longer term backups and archiving. My NT
>admins like me because I use the tape drives during daylight hours and the
>network at night, both of which are low activity times for the respective
>resources.
>--
>Geoff N. Hiten
>Senior Database Administrator
>Microsoft SQL Server MVP
>
>"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
>news:elfo4aaZGHA.3652@.TK2MSFTNGP03.phx.gbl...
>> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
>> news:eHCiDCXZGHA.4248@.TK2MSFTNGP05.phx.gbl...
>> This may work, but it is not guaranteed.
>> I would backup to a USB drive or a UNC share across the network. If you
>> don't have the space to handle a backup copy and a restore copy of the
>> database, you really shouldn't be running SQL Server.
>> We use the UNC share method all the time and in fact just upgraded to a
>> Snap
>> 4200 server.
>> Works very nicely for us. Not the fastest solution out there, but almost
>> 3
>> times faster than our Snap Server 4100 was, so I'm happy.
>> (100+ gig in about 3 hours).
>>
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?id=555128
>>
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Randy K" <wawork@.hotmail.com> wrote in message
>> news:44491b1e.70492156@.msnews.microsoft.com...
>> >I have a question considering restores. If I take my database offline
>> > the backup system will backup the .mdf and .ldf files making up the
>> > database. Can these files be used to restore a SQL 2000 database.
>> >
>> > The reason I ask is that we don't have enough space to create a full
>> > back up and we cannot write directly to a tape device. I have no
>> > problem detaching the database, letting that backup, then reattaching
>> > it. However, if I am not here I'd rather leave instructions for my
>> > boss to just take the database offline when he leaves for the weekend
>> > and bring it back online Monday morning.
>> >
>> >
>>
>>
>

Restoring from .MDF/.LDF

Hi there,

I've got a .mdf/.ldf database/log. I'm trying to bring it into SQL Server Management studio, but can't figure out how. I've gotten as far as "Attaching" it, but can't figure out where to go from there. Can anyone help me out. I know this is very basic.

Thanks.

First of all, you cannot "Restore from MDF files"

Hi,

place the mdf /ldf file somewhere you want to keep them in the future. Then you will have to use the attach dialog you already found and attach the database files. If the logfile path points to another direction than it is currently stored in, change it and click ok. SQL Server will attach the database and open it for further use.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Moving database from SQLS2k5 to SQLS2k5Express may be not easy!

I have obtained customer DB which was detached from SQL2K and attached it to SQL2005 Express.

I found that all tables are presented in form DBA.<table name> and simplest query

SELECT * FROM <table name>

results in error message saying something like "object name <table name> is incorrect" while query

SELECT * FROM DBA.<table name>

goes successfully.

Well, what should be done in this case?

Thank you.

|||

Hi,

Try using "USE [DB_NAME]" command before using SELECT statement.

Hope this would help.

Shakeel

|||Thanks everyone. This was all very helpful!