Friday, September 7, 2012

Honey I have restored the database

A few weeks ago I published the blog Honey I Shrunk the Database, and I mentioned a way of shrinking your database.
The method to be discussed during this blog was inspired by - PaulRandal – “Why You Should NotShrink Your Data Files”

Let’s begin and call it steps by which you can shrink your data files:
1.       Create a new filegroup
a.       Right click on the database you want to add the new file group to.
b.      In the select page option, select the option files.
c.       Click the add button and a new file group will be added.
d.      Click on the file group textbox that will give you two options, select the option:  <new filegroup>
After completion of these steps your display should look like this….

2. 2.       Give your new file group a Logical name and also select which file group it needs to be a part of.
3.       Then hit the select button.
The easy part is done, now the process of moving everything from the primary to the new filegroup can begin.
For the table moving script:
Moving all the tables and their dependencies to the new file group, I consulted a developers best friend “Google” to find a piece of code that I can use to move everything in the tables and Google led me to the following blog:

SQL Server: Move Tables between Filegroups
All the credit to the developer who created this script and thank you. Click on the link, read the comments and all information about the script.
To make the script work for me, I changed it up a bit, adding in extra code so that it can loop through my database and move everything.  With all the extra code I have added it failed when I executed it, so I am not going to post my broken script, I would rather let you use the link above and play around with the script by yourself.
The creator of the script also gives you a table of platforms on which the script works.
4.       After you run the above script or piece of code or any code that you want to use. If you have a better way or alternative way of doing it please share it with us!
Drop the old filegroup that you were going to shrink anyway (or shrink it way down if it’s the primary filegroup)

No comments:

Post a Comment