restore data file only

23 Dec 2005 3:47 AM

Datasort

I am trying to restore a data file and not the log file. Acutally, the log
file will max out my drive space so i am trying to create a new db from a
backup without the log file. Any thoughts?

Thanks in advance,

--
Stewart Rogers
DataSort Software, L.C.

23 Dec 2005 7:46 AM

John Bell

Hi

You don't say if this is the only data file?

Try:
RESTORE DATABASE database_name
FILE = logical_file_name
FROM DISK 'physical_backup_device_name'

See Books online for more information on the restore command

John

Show quote

"Datasort" wrote:

> I am trying to restore a data file and not the log file. Acutally, the log
> file will max out my drive space so i am trying to create a new db from a
> backup without the log file. Any thoughts?
>
> Thanks in advance,
>
>
> --
> Stewart Rogers
> DataSort Software, L.C.

23 Dec 2005 4:17 PM

Datasort

John First .. thanks for your reply

The backup is from a client's machine. The problem is that the the log file
is 7 gig but the data file is 2 gig. I have about 4 gig to work with.

I tried this code and please see the error below:

RESTORE DATABASE horse_01
FILE = 'horse_01_data'
FROM DISK = 'E:\Horse\horse_01'

Error:
Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing
'horse_01' database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Any other thoughts?

--
Stewart Rogers
DataSort Software, L.C.

Show quote

"John Bell" wrote:

> Hi
>
> You don't say if this is the only data file?
>
> Try:
> RESTORE DATABASE database_name
> FILE = logical_file_name
> FROM DISK 'physical_backup_device_name'
>
> See Books online for more information on the restore command
>
> John
>
> "Datasort" wrote:
>
> > I am trying to restore a data file and not the log file. Acutally, the log
> > file will max out my drive space so i am trying to create a new db from a
> > backup without the log file. Any thoughts?
> >
> > Thanks in advance,
> >
> >
> > --
> > Stewart Rogers
> > DataSort Software, L.C.

24 Dec 2005 5:55 PM

John Bell

Hi

Sorry that was a bum steer, there is no way to do this without restoring the
log file. If you can find a machine with sufficient capacity then you could
restore it on that, detach the database and then re-attach the mdf file on
it's own. To do this you may have to temporarily stick an extra disc into the
box.

See sp_attach_single_file_db in Book Online and check out
http://support.microsoft.com/kb/224071/#XSLTH3125121122120121120120

John

Show quote

"Datasort" wrote:

> John First .. thanks for your reply
>
> The backup is from a client's machine. The problem is that the the log file
> is 7 gig but the data file is 2 gig. I have about 4 gig to work with.
>
> I tried this code and please see the error below:
>
> RESTORE DATABASE horse_01
> FILE = 'horse_01_data'
> FROM DISK = 'E:\Horse\horse_01'
>
>
> Error:
> Server: Msg 3154, Level 16, State 1, Line 1
> The backup set holds a backup of a database other than the existing
> 'horse_01' database.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Any other thoughts?
>
> --
> Stewart Rogers
> DataSort Software, L.C.
>
>
> "John Bell" wrote:
>
> > Hi
> >
> > You don't say if this is the only data file?
> >
> > Try:
> > RESTORE DATABASE database_name
> > FILE = logical_file_name
> > FROM DISK 'physical_backup_device_name'
> >
> > See Books online for more information on the restore command
> >
> > John
> >
> > "Datasort" wrote:
> >
> > > I am trying to restore a data file and not the log file. Acutally, the log
> > > file will max out my drive space so i am trying to create a new db from a
> > > backup without the log file. Any thoughts?
> > >
> > > Thanks in advance,
> > >
> > >
> > > --
> > > Stewart Rogers
> > > DataSort Software, L.C.

23 Dec 2005 9:27 AM

Tibor Karaszi

If you have a SQL Server backup file (produced by the SQL BACKUP command), you are out of luck. SQL
Server need to create as many files each with at least the same size as what the db had when you
took the backup.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Datasort" <Datas***@discussions.microsoft.com> wrote in message
news:CBD1534C-8081-4961-8BA5-FD82B3075A61@microsoft.com...
>I am trying to restore a data file and not the log file. Acutally, the log
> file will max out my drive space so i am trying to create a new db from a
> backup without the log file. Any thoughts?
>
> Thanks in advance,
>
>
> --
> Stewart Rogers
> DataSort Software, L.C.