March 15, 2013 at 3:14 am
how to restore a database of size 30 gb with the backup size of 10 GB of disk space 15 gb in sql
March 15, 2013 at 3:22 am
ramyours2003 (3/15/2013)
how to restore a database of size 30 gb with the backup size of 10 GB of disk space 15 gb in sql
If I am getting it right.. The database size is 30 Gb and the backup size is 10 Gb and you only have 15 Gb of available free space.
What is the actual free space in the database? If it is less then 15 GB, you can try to shrink the data and log files so that they are under 15 Gb and then, take a backup.
Else "get some space" :).
You can
get the drive size increased,
If there are other drives on the server having enough space then, restore the database using "with move" option to place the physical files on different locations.
March 15, 2013 at 3:54 am
Restore somewhere that has 30 GB free, shrink the DB, then move it or backup/restore again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2013 at 4:47 am
It's worth looking at the log sizes first prior to the backup being taken.
If the log has been left unmaintained, then there's a good chance it is accounting for a lot of the overall space being used.
If it's the data file that is 30GB on its own (use Bill Graziano's "BigTables" Script to determine the data you've got in there;
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* v1.1
*
**************************************************************************************/
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
create table #spt_space
(
objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
--***********************************************
Then you're going to have to find the space somewhere I'm afraid.
Are you trying to overwrite the primary db?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply