SQL Server Database Guru - Shrink SQL Database During Restore

It's time for another July TechNet Guru winner!

Congratulations to Pituach, our SQL Server Database Engine Guru winner for July! See the TechNet Guru Contributions for July 2013.


About Pituach: Ronen Ariely,  Programmer professional, SQL & BI Architect. with more than 12 years of experience in variety of programming languages and technologies, leading and managing development teams and BI projects.


Here is the gold-medal-winning article:

Shrink SQL Database During Restore


Now let's look at all the winning articles:

Guru Award  SQL Server Database Engine Technical Guru - July 2013 
Gold Award Winner  pituach Shrink SQL Database during Restore Peter Laker: "Excellent article, lots of detail."Ed Price: "The case study section and strong explanations help bring this article to the top... not to mention a fantastic subject! Great job!"
Silver Award Winner  Uwe Ricken SQL Server: No Fragmentation in HEAP from INSERT / UPDATE Peter Laker: "Nice tip, well presented code."Ed Price: "A great solution with well-formatted code, and well-placed images. If it included more explanations, it would be perfect. A very good article!"
Bronze Award Winner  rksqldba SQL VDI Backups fail with Error 9001 Peter Laker: "Nice copy in from forum help. Just what we need."Ed Price: "This is a good, straightforward solution."


Congratulations to these three. Some great knowledge shared with the community. Mohammad Nizamuddin just missed out on a medal for July, so hopefully we'll see him return and try again for August!



And here's an excerpt from the article:


Step 2: Backup your database

Copy the backup file to the compress folder temporary. We can see on both folders (the compressed and the regular) that the file size is the same 200 MB. But closer look at the "size on disk" can show us the difference. The compressed folder is only 82.7 MB.

Backup our database directly to the compressed folder working great, but will restore into the compressed folder work?

Let's detach the database and try to restore our database firstly to the UnCompressed folder, just as checkpoint that we don’t have any problem (I created folder named "UnCompress"):

USE [master]

RESTORE ``DATABASE [AdventureWorksDW2012]

FROM  DISK = N``'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\AdventureWorksDW2012.bak'

 ``WITH  FILE = 1, 


 ``TON``'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\UnCompressFolder\AdventureWorksDW2012_Data.mdf'`` ,


  ``TON``'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\UnCompressFolder\AdventureWorksDW2012_log.ldf'`` , 







Read the rest here:

Shrink SQL Database During Restore  


Thanks to Pituach for your great contribution to the TechNet Guru contest! You can read about all the July winners here: TechNet Guru Awards - July 2013


Also, for the August Guru competition, see TechNet Guru Contributions - August 2013


Are you a Wiki Ninja? http://technet.com/wiki

- User Ed