Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup / restore issue


Backup / restore issue

Author
Message
Jus
Jus
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
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
Sarab_SQLGeek
Sarab_SQLGeek
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Jus
Jus
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
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.
Sarab_SQLGeek
Sarab_SQLGeek
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Sudeepta
Sudeepta
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
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
Sarab_SQLGeek
Sarab_SQLGeek
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 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. Unsure

Regards,
Sarabpreet Singh Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Steve-3_5_7_9
Steve-3_5_7_9
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1584
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search