Can I restore just a data file (not the transaction log)

I have a 33 gig backup file that I need to restore. I think it's so
large because when the SQL 2000 database was backed up, it wasn't backed
up in a while and the transaction log was quite large. So can I restore
just the data file?
Here is some info:
When I do the RESTORE FILELIST only, I get:
RESTORE FILELISTONLY
FROM DISK = 'g:\MEIS'
MEIS_dat E:\MSSQL7\DATA\MEIS.mdf D PRIMARY 46424915968 35184372080640
MEIS_log E:\MSSQL7\DATA\MEIS.ldf L NULL 3002073088 35184372080640
You see the size of the log file is 46 gig. But the size of the data
file is 3 gig.
The syntax that I came up with (but I have not tried, because I am not
at that point yet so I wanted to find out if it will work) is:
RESTORE DATABASE MEIS
FROM DISK = 'g:\MEIS'
WITH MOVE 'MEIS_dat' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\MEIS.mdf'
And just leave the line off dealing with the transaction log. So will
this restore just the data and not the transaction log?
The database was using either a Full or Simple recovery model, I am not
sure.
Thanks
-C
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Restore database just restores the database. It's only if you specify
NORECOVERY for the database restore, that you can restore additional backups
(eg log backups). If the database was using the simple recovery model, the
transaction log is automatically truncated. The log is probably so large
because SQL does shrink the file and return the empty space back to the OS
unless you issue a DBCC SHRINKFILE command on the log file. Your restore
command should work provide the device names and OS file locations are ok.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Colin Colin" <ccole@.ghs.guthrie.org> wrote in message
news:e0YB8UIfDHA.2236@.TK2MSFTNGP12.phx.gbl...
> I have a 33 gig backup file that I need to restore. I think it's so
> large because when the SQL 2000 database was backed up, it wasn't backed
> up in a while and the transaction log was quite large. So can I restore
> just the data file?
> Here is some info:
> When I do the RESTORE FILELIST only, I get:
> RESTORE FILELISTONLY
> FROM DISK = 'g:\MEIS'
> MEIS_dat E:\MSSQL7\DATA\MEIS.mdf D PRIMARY 46424915968 35184372080640
> MEIS_log E:\MSSQL7\DATA\MEIS.ldf L NULL 3002073088 35184372080640
> You see the size of the log file is 46 gig. But the size of the data
> file is 3 gig.
>
> The syntax that I came up with (but I have not tried, because I am not
> at that point yet so I wanted to find out if it will work) is:
> RESTORE DATABASE MEIS
> FROM DISK = 'g:\MEIS'
> WITH MOVE 'MEIS_dat' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MEIS.mdf'
> And just leave the line off dealing with the transaction log. So will
> this restore just the data and not the transaction log?
> The database was using either a Full or Simple recovery model, I am not
> sure.
> Thanks
> -C
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||RESTORE will re-create all files, with the same size as they had when you took the backup. The MOVE
option will only allow you to specify a new logical name. Not specifying MOVE mean that SQL Server
will keep the same logical name.
To shrink log, Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Colin Colin" <ccole@.ghs.guthrie.org> wrote in message news:e0YB8UIfDHA.2236@.TK2MSFTNGP12.phx.gbl...
> I have a 33 gig backup file that I need to restore. I think it's so
> large because when the SQL 2000 database was backed up, it wasn't backed
> up in a while and the transaction log was quite large. So can I restore
> just the data file?
> Here is some info:
> When I do the RESTORE FILELIST only, I get:
> RESTORE FILELISTONLY
> FROM DISK = 'g:\MEIS'
> MEIS_dat E:\MSSQL7\DATA\MEIS.mdf D PRIMARY 46424915968 35184372080640
> MEIS_log E:\MSSQL7\DATA\MEIS.ldf L NULL 3002073088 35184372080640
> You see the size of the log file is 46 gig. But the size of the data
> file is 3 gig.
>
> The syntax that I came up with (but I have not tried, because I am not
> at that point yet so I wanted to find out if it will work) is:
> RESTORE DATABASE MEIS
> FROM DISK = 'g:\MEIS'
> WITH MOVE 'MEIS_dat' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\MEIS.mdf'
> And just leave the line off dealing with the transaction log. So will
> this restore just the data and not the transaction log?
> The database was using either a Full or Simple recovery model, I am not
> sure.
> Thanks
> -C
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!