Sql server备份对于express来说太大了 [英] Sql server backup is too big for express
问题描述
为了让我能够在家工作，我需要一份合同公司的数据 - 它不需要是最新的复制结构，数据库只有在其主表中的3500条记录的区域内但是当我使用SSMS创建备份时，生成的文件是15 gig，这对我来说太大了，无法在我的SQL Server Express版本上恢复，有关如何减小尺寸的任何想法？我不需要日志，但看不到排除它的方法。
我尝试了什么：
使用SSMS创建备份太大而无法恢复
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
推荐答案
Sql Server express对数据库有大小限制： SQL Server Express - 维基百科，免费的百科全书 [ ^ ]
Sql Server express has a size limit for databases : SQL Server Express - Wikipedia, the free encyclopedia[^]
查看实际文件大小&数据库恢复模型。
当数据库处于完全恢复状态时事务日志没有备份它会不断增长。
如果你看到MDF文件（例如）1GB&你的日志文件是14GB然后你需要备份&截断日志文件 - 请参阅以下关于日志文件管理的MSDN文章
[ ^ ]
根据最适合您的方式，以下任何一项都可以使用。
1.备份&截断日志文件，然后备份你的数据库
或
2.将恢复模型设置为Simple&然后备份数据库 - 这将起作用，因为一旦提交了事务，当数据库处于简单模式时可以重用日志文件空间，如果它处于完全模式，则需要在事务日志空间可以之前备份日志文件重新使用。
亲切的问候
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
我正在接受
它不需要是最新的复制结构，
it doesn't need to be an up to date copy just the structure,
鉴于只有大约3500条记录你认为只是创建一些脚本创建所有表然后生成一些脚本来填充表...例如例如使用SQL Server生成来自特定表数据的插入语句 [ ^ ]
Microsoft do类似于生成样本数据库的东西，例如从官方Microsoft下载中心下载Northwind和pubs SQL Server 2000示例数据库 [ ^ ]
另请参阅如何：生成脚本（SQL Server管理） Studio） [ ^ ]
这是我亲自使用的一种方法，只要您不需要任何最新更新，并且不想使用GETDATE（）随时;-p
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
这篇关于Sql server备份对于express来说太大了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
