0

My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to run DBCC SHRINKFILE with TRUNCATEONLY before restoring differential backup.

Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY after full backup restored but before differential backup restoration.

  • Please consider reading the following article: How do I ask a good question? . Currently your question doesn't contain enough relevant information to be answerable and will possibly be closed as unclear what you're asking. To answer your question: You can always run the SQL Server commands against a database. The questions you probably should be asking are: Should I run the DBCC SHRINKFILE... command before or after the restore? and Should I run the DBCC SHRINKFILE... at all? – hot2use Jul 24 '18 at 5:35
  • Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY between full backup restored and before differential backup restoration. – kalpna Jul 24 '18 at 5:45
0

Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY between full backup restored and before differential backup restoration.

No, you cannot.

You'll get the error that your database is in the middle of restore, and this is logic, you cannot do anything with database that is restoring.

You should analyse your db free space using sp_spaceused to understand if you can and should do anything with this database BEFORE you take the last backup, may be your db is in the full recovery model and log backups are not made regularly?

0

Shrinking database has to be done after restore backups (any backup) or before taking the backup.

And you should Restore data-files as they were at the backup.

The following steps might give you what you need and shouldn't impact production:

  • Run a DBCC SHRINKFILE ('.MDF', TRUNCATEONLY) on the production data file to temporarily release any free space at the end of the file (a TRUNCATEONLY is not IO intensive, and will not fragment indexes)

  • If the log file is also big, run a DBCC SHRINKFILE on the production log file during a time of low activity, just after taking a log backup.

  • After Run your backup
  • Do an ALTER DATABASE MODIFY FILE to re-grow the production data file back to the original size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.