0

I have a sample SQL Server database backup that is ~12gb. Too large for me to restore in SQL Server Express (10gb limit). I downloaded the Developer version of SQL Server (on my home PC) and was able to restore the instance there, but the machine I need to test this database on is 32-bit and I can't find a 32-bit SQL Server Developer edition.

Is there an easy way for me to reduce the database size by a couple of GB by truncating some tables I'm not using in order to get under the 10gb limit so I can restore it to a 32-bit version of SQL Server Express?

I've already cleared out the data I don't need, But when I make a full backup, it's like ~50gb! Is there a way to get a smaller backup after removing some data? what settings should I use to backup?

This data is just for testing with a Windows form app I'm creating so it's most important that I get the schema and stored procedures over anyways. I've tried to generate scripts and run them on the new SQL server instance, but there's these dependencies to some .mdf file that I can't move that causes it to fail.

Let me know if I can provide any additional helpful information.

Any help would be appreciated - thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
EMUEVIL
  • 502
  • 3
  • 14
  • 1
    If it's for testing, you could drop some of the indexes on those tables and then shrink the database. https://msdn.microsoft.com/en-us/library/ms190488.aspx – manderson Feb 16 '17 at 19:41
  • Possible duplicate of [How do I shrink my SQL Server Database?](http://stackoverflow.com/questions/439071/how-do-i-shrink-my-sql-server-database) – Daniel Corzo Feb 16 '17 at 19:42
  • @manderson I'll try deleting some indexes. I've already cleared a log table that had 25+ million rows. Do you know if a full backup will be the correct size? when I first did a full backup, it went from 12gb up to 50. It appears not all that space is used, it's just allocated to the database. Is there a way to not have a huge backup. I'm just trying to transfer the DB over but it needs to be under 10gb. – EMUEVIL Feb 16 '17 at 19:45
  • Try shrinking the database and following any steps in the link @DanielCorzo gave you. How did you delete those records? With a delete statement? http://www.stackbuddy.com/reduce-database-size-truncate-delete-ms-sql/ – manderson Feb 16 '17 at 19:48
  • If logging is not important and you are not in simple recovery mode then you can change your recovery mode to simple and shrink the logs. – Ross Bush Feb 16 '17 at 19:51
  • @manderson I deleted the data in the large log table with the TRUNCATE TABLE command. It happened immediately so clearly it didn't take the time to actually delete the data, just unallocate. I tried to rebuild the index after but it just made my poor laptop explode on disk usage 100% for 10 minutes. I'm going to look into the database Shrink and changing the recovery mode to simple. Thanks everyone! I'll let you know how it goes. – EMUEVIL Feb 16 '17 at 20:14
  • EMUEVIL, as a suppliment to Gordon's answer, you could use DTS Wizard and export your largest table(s) to a text file, then TRUNCATE the table(s) you've exported. Then take your backup. ( You may also ZIP the exported files if necessary.) This allows you to reduce the size of the backup, and yet keep all the data, by importing the data back in once you've RESTORED to your destination system. – Juan-Carlos Feb 21 '17 at 13:14

1 Answers1

0

In Developer Edition, right-click Database Name, select Reports > Standard Reports > Disk Usage by Top Tables. That will show which tables are taking up the most room.

Remove what you can, then shrinking your data and log files using: Right-Click Database Name > Tasks > Shrink > Files:

Do this twice, once for File Type: Data and second for File Type: Log

Create new backup and see if it's within limit.

Gordon Bell
  • 13,337
  • 3
  • 45
  • 64