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!

Can I pick your brains for a second? transferring a large database problem.

Okay, I have a problem that I need some suggestions with.
I have a 30 Gig database that I need to sync across a T1 every night. This
database is a data dump of our billing system data, the tables are dropped
ad re-created every night.
I can do a very long and laborious process on the server of creating tables
with the primary keys from each table and a binary checksum of a row, then
complaining it to a copy of the database from the day before. Then I
generate SQL statements for the updates (Actually deletes and inserts). All
of this on the source server.
I'd like to compact this down using a DTS package on the destination side,
however I can't use a dynamic query in the "openRowSet" command to get only
the row that I want.
Here is what I'm doing (Just for one table, I'll have data driven steps for
each table in my DB)
insert into @.tempChecker
select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
chgno from charge_t')
Declare Charge_t_Cursor Cursor
FOR
select
a.chgno
from
(select
BSum,
chgno
from @.tempChecker
) a
where
not exists
(
select 'x'
from charge_t b
where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
)
Once I get all the chgno's (The key in the table) I want to only pull back
the data that has changes. So I have this (Which I know I can't do put you
get the idea)
Open Charge_t_Cursor
Fetch next from Charge_t_cursor into @.ChgNo
While @.@.FETCH_STATUS = 0
BEGIN
insert into @.ChargeT select *
from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
cast(isnull(@.ChgNo, 0) as varchar)))
Fetch next from Charge_t_cursor into @.ChgNo
END
Close Charge_t_cursor
deallocate Charge_t_cursor
select * from @.ChargeT
this way I'm only pulling over the wire the data that is updated and it will
be in a nice, compact binary format. ANY help would be VERY appreciated!!!
Thanks much!
Scott
have you tried to export your tables into flat files, compact these files
(using winzip or anything else)
copy the files and load these files on the destination server.
you could reduce the tranfert from 30gb to 1Gb.
more complex then a DTS pump, but this could be a good solution.
another way...
you can use replication.
or you can create your own "replication" model, I mean add triggers on your
table to log changes into another table in your source system, then load
these changes only every night.
Have you a "last update date" colum in your source tables?
"Scott M" <scott@.nospam.com> wrote in message
news:ezl3f2saFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Okay, I have a problem that I need some suggestions with.
> I have a 30 Gig database that I need to sync across a T1 every night.
> This database is a data dump of our billing system data, the tables are
> dropped ad re-created every night.
> I can do a very long and laborious process on the server of creating
> tables with the primary keys from each table and a binary checksum of a
> row, then complaining it to a copy of the database from the day before.
> Then I generate SQL statements for the updates (Actually deletes and
> inserts). All of this on the source server.
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only the row that I want.
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for each table in my DB)
> insert into @.tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @.tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you get the idea)
> Open Charge_t_Cursor
> Fetch next from Charge_t_cursor into @.ChgNo
> While @.@.FETCH_STATUS = 0
> BEGIN
> insert into @.ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@.ChgNo, 0) as varchar)))
> Fetch next from Charge_t_cursor into @.ChgNo
> END
> Close Charge_t_cursor
> deallocate Charge_t_cursor
> select * from @.ChargeT
> this way I'm only pulling over the wire the data that is updated and it
> will be in a nice, compact binary format. ANY help would be VERY
> appreciated!!! Thanks much!
> Scott

Can I pick your brains for a second? transferring a large database problem.

Okay, I have a problem that I need some suggestions with.
I have a 30 Gig database that I need to sync across a T1 every night. This
database is a data dump of our billing system data, the tables are dropped
ad re-created every night.
I can do a very long and laborious process on the server of creating tables
with the primary keys from each table and a binary checksum of a row, then
complaining it to a copy of the database from the day before. Then I
generate SQL statements for the updates (Actually deletes and inserts). All
of this on the source server.
I'd like to compact this down using a DTS package on the destination side,
however I can't use a dynamic query in the "openRowSet" command to get only
the row that I want.
Here is what I'm doing (Just for one table, I'll have data driven steps for
each table in my DB)
insert into @.tempChecker
select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
chgno from charge_t')
Declare Charge_t_Cursor Cursor
FOR
select
a.chgno
from
(select
BSum,
chgno
from @.tempChecker
) a
where
not exists
(
select 'x'
from charge_t b
where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
)
Once I get all the chgno's (The key in the table) I want to only pull back
the data that has changes. So I have this (Which I know I can't do put you
get the idea)
Open Charge_t_Cursor
Fetch next from Charge_t_cursor into @.ChgNo
While @.@.FETCH_STATUS = 0
BEGIN
insert into @.ChargeT select *
from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
cast(isnull(@.ChgNo, 0) as varchar)))
Fetch next from Charge_t_cursor into @.ChgNo
END
Close Charge_t_cursor
deallocate Charge_t_cursor
select * from @.ChargeT
this way I'm only pulling over the wire the data that is updated and it will
be in a nice, compact binary format. ANY help would be VERY appreciated!!!
Thanks much!
Scotthave you tried to export your tables into flat files, compact these files
(using winzip or anything else)
copy the files and load these files on the destination server.
you could reduce the tranfert from 30gb to 1Gb.
more complex then a DTS pump, but this could be a good solution.
another way...
you can use replication.
or you can create your own "replication" model, I mean add triggers on your
table to log changes into another table in your source system, then load
these changes only every night.
Have you a "last update date" colum in your source tables?
"Scott M" <scott@.nospam.com> wrote in message
news:ezl3f2saFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Okay, I have a problem that I need some suggestions with.
> I have a 30 Gig database that I need to sync across a T1 every night.
> This database is a data dump of our billing system data, the tables are
> dropped ad re-created every night.
> I can do a very long and laborious process on the server of creating
> tables with the primary keys from each table and a binary checksum of a
> row, then complaining it to a copy of the database from the day before.
> Then I generate SQL statements for the updates (Actually deletes and
> inserts). All of this on the source server.
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only the row that I want.
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for each table in my DB)
> insert into @.tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @.tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you get the idea)
> Open Charge_t_Cursor
> Fetch next from Charge_t_cursor into @.ChgNo
> While @.@.FETCH_STATUS = 0
> BEGIN
> insert into @.ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@.ChgNo, 0) as varchar)))
> Fetch next from Charge_t_cursor into @.ChgNo
> END
> Close Charge_t_cursor
> deallocate Charge_t_cursor
> select * from @.ChargeT
> this way I'm only pulling over the wire the data that is updated and it
> will be in a nice, compact binary format. ANY help would be VERY
> appreciated!!! Thanks much!
> Scott