Click here to Skip to main content
15,888,113 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
To enable me to work from home I need a copy of my contract companies data - it doesn't need to be an up to date copy just the structure, the database only has in the region of 3500 records in its main tables but when I created a backup using SSMS the resultant file was 15 gig which is then too big for me to restore on my SQL Server Express edition , any ideas on how to reduce the size ? I don't need the log but can't see a way of excluding it.

What I have tried:

Creating a backup using SSMS which was too big to restore
Posted
Updated 28-Apr-16 13:12pm

Sql Server express has a size limit for databases : SQL Server Express - Wikipedia, the free encyclopedia[^]
 
Share this answer
 
Comments
pkfox 27-Apr-16 2:46am    
Hi there I realise there is a limit but no way is this database 15 gig there must be a very large log file that I don't need
Take a look at the actual file sizes & the Database Recovery model.
When a DB is in Full Recovery & the Transaction Log is not being backed up it will continually grow.
If you see you MDF file is (for instance) 1GB & your log file is 14GB then you need to backup & truncate the log file - refer the following MSDN article on Log File management
[^]

Depending on what works best for you either of the following things will work.
1. Backup & truncate the Log file, then Backup your database
or
2. Set the Recovery model to Simple & then backup the database - this will work because once the transactions have been committed the Log file space can be reused when the DB is in Simple mode, if it is in Full mode you need to backup the Log file before the transaction log space can be reused.

Kind Regards
 
Share this answer
 
Comments
pkfox 29-Apr-16 1:14am    
Although this is a Sql server database it's a Maximiser CRM database and i think the extra baggage of Maximiser is the culprit. Tnaks for your time.
I'm picking up on
Quote:
it doesn't need to be an up to date copy just the structure,

Given that there are only about 3500 records have you considered just creating some scripts to create all of the tables then generating some script to populate the tables ... e.g. like Generate insert statements from a specific table data using SQL Server[^]
Microsoft do something similar to generate their sample databases e.g. Download Northwind and pubs Sample Databases for SQL Server 2000 from Official Microsoft Download Center[^]
See also How to: Generate a Script (SQL Server Management Studio)[^]
This is a method I have personally used and it worked ... as long as you don't need any recent updates and don't want to use GETDATE() anytime ;-p
 
Share this answer
 
Comments
an0ther1 29-Apr-16 0:24am    
Nice answer, I completely missed the "...just the structure" statement
pkfox 29-Apr-16 1:15am    
Hi there please see my answer to the other chap. Thanks for your time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900