Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, April 1, 2016 11:24 AM
Points: 368, Visits: 558
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
Sarabpreet.com
SQLChamp.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, April 1, 2016 11:24 AM
Points: 368, Visits: 558
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
Sarabpreet.com
SQLChamp.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, April 1, 2016 11:24 AM
Points: 368, Visits: 558
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
Sarabpreet.com
SQLChamp.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: Monday, September 19, 2016 7:14 AM
Points: 967, Visits: 1,560

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