Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup / restore issue Expand / Collapse
Author
Message
Posted Wednesday, September 2, 2009 8:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
Points: 124, Visits: 275
Hello Guys,

I have an issue with backup / restore process. The issue as under:

I have a SQL Server 2005 Database in Production, for which InitailSize is set to 60 Gig. Infact the DB is not that big and when i have taken the full backup of this database, The backup file size is shown as 1 gig only.

Now the issue is, My QAT environment doesnot have 60 gig free space. it has only 10 gig of free space.

I want this DB to restored in QAT environment with initial size say 4 or 5 gig. how will i do that?

Script for the same would be appreciated.

--Jus
Post #781529
Posted Wednesday, September 2, 2009 9:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
There are many ways to resolve this:
Ist is try to shrink this DB and then take a fresh backup which can be restored at the destination(QA) Server. [but this can result in fragmentation]

2nd is if the DB has only 1GB of data you should create a fresh DB with initial size 1 GB and do enable the autogrowth but only by 10% and then shift all your data to that DB and then take a fresh backup and restore it.



Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #781557
Posted Wednesday, September 2, 2009 9:09 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 29, 2010 2:48 AM
Points: 124, Visits: 275
First option is not possible, because prod environment is not in my control.

Second, I will be getting the backup file only from production, nothing else.

Any changes can be performed in QAT while restoring. Please let me know if this can be done ( i mean change the initialsize to 3 mb while restore itself).....I have the access only to QAT to make any changes while restoring.
Post #781565
Posted Wednesday, September 2, 2009 10:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
as per my knowledge NO

but you can ask the prod team to export the prod data to new DB of your QA Server.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #781638
Posted Thursday, September 3, 2009 2:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 12:34 AM
Points: 188, Visits: 189
You have a full backup of size 1GB. Create a database on the QA environment with the same logical file name as of the Production DB. Restore the database and then shrink it.

Script:

RESTORE DATABASE [DATABASE_NAME]
FROM DISK = 'Path_of_the_backupfile',
MOVE 'Logical_datafilename' TO 'Physical_datafile_location',
MOVE 'Logical_logfilename' TO 'Physical_logfile_location',
REPLACE
GO



Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
Post #781980
Posted Thursday, September 3, 2009 2:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
Sudeepta (9/3/2009)
You have a full backup of size 1GB. Create a database on the QA environment with the same logical file name as of the Production DB. Restore the database and then shrink it.

Script:

RESTORE DATABASE [DATABASE_NAME]
FROM DISK = 'Path_of_the_backupfile',
MOVE 'Logical_datafilename' TO 'Physical_datafile_location',
MOVE 'Logical_logfilename' TO 'Physical_logfile_location',
REPLACE
GO



The initial size of the file is 60 GB and They don't have that much space on their QA Server.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #781999
Posted Friday, September 4, 2009 8:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 914, Visits: 1,430

It looks like you have 2 options.
1. get more space
2. Have the prod dba import the data for you (as mentioned above)

I doubt they would shrink a production database as that could cause fragmentation and it was probably created that large for a reason.




Post #782948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse