|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Wednesday, February 10, 2010 2:19 AM
Points: 114,
Visits: 228
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: 2 days ago @ 11:45 AM
Points: 254,
Visits: 265
|
|
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 Anand 
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, February 10, 2010 2:19 AM
Points: 114,
Visits: 228
|
|
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.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: 2 days ago @ 11:45 AM
Points: 254,
Visits: 265
|
|
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 Anand
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Friday, December 04, 2009 8:52 AM
Points: 33,
Visits: 57
|
|
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.
e@: sudeeptaganguly@live.com
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: 2 days ago @ 11:45 AM
Points: 254,
Visits: 265
|
|
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 Anand
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 615,
Visits: 521
|
|
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.
|
|
|
|
