微软的FTE也有加班到很晚，有的也是成家有老婆孩子的。

高薪之下，也有压力，换个环境，未必能有这么好的待遇。

机遇在天，但是奋斗不可止呼

　　SELECTbackup_size/compressed_backup_sizeAScompression_ratio,*
　　FROMmsdb..backupset;

　　在backupmediaset表中，is_compressed字段告诉你这个备份是否是压缩的。尽管你可以依靠这些字段来确定一个备份媒体是否被压缩了，但是还是推荐使用合适的名字来命名备份媒体，以便不会在发生灾难时被使用哪个媒体混淆。你还可以在创建数据库维护计划时使用备份压缩。

　　让我们比较下压缩备份和未压缩备份的恢复过程。我将使用一个不同的名称和MOVE选项来开始恢复未压缩备份，所以我不会重载用于原始AdventureWorks数据库的已有MDF和LDF文件。　

　　　USEmaster
　　GO
　　RESTOREDATABASEAdventureWorks_uncompressed
　　FROMDISK=N'C:SQLDataAdventureWork_uncompressed.bak'
　　WITHMOVEN'AdventureWorks_Data'TON'C:SQLDataAdventureWorks.mdf',
　　MOVEN'AdventureWorks_Log'TON'C:SQLDataAdventureWorks_1.ldf',
　　RECOVERY,
　　STATS=10;
　　GO

　　未压缩备份的恢复过程花费的时间大约为39.72秒。

　　图5　

　　现在我们将恢复压缩数据库备份。注意，它使用的是和我们在未压缩数据库备份中所使用的相同的RESTORE DATABASE命令，因为SQL Server可以从备份媒体元数据了解到它是否是压缩的。　　

　　　USEmaster
　　GO
　　RESTOREDATABASEAdventureWorks_compressed
　　FROMDISK=N'C:SQLDataAdventureWorks_compressed.bak'
　　WITHMOVEN'AdventureWorks_Data'TON'C:SQLDataAdventureWorks_c.mdf',
　　MOVEN'AdventureWorks_Log'TON'C:SQLDataAdventureWorks_c.ldf',
　　RECOVERY,
　　STATS=10;
　　GO

　　压缩数据库的恢复过程花费的时间大约为22.03秒。即在恢复时间上降低了45%。　

　　图6

　　激活服务器实例级别的数据库备份压缩

　　因为数据库备份压缩默认情况下是关闭的，所以你可以选择使用sp_configure系统存储过程来激活服务器实例级别的数据库备份压缩。　

　　　USEmaster
　　GO
　　EXECsp_configure'backupcompressiondefault','1'
　　RECONFIGUREWITHOVERRIDE

　　你还可以通过使用SQL Server管理套件来设置服务器实例属性来激活。在Server Properties 窗口的Database Settings部分，只要点击Compress Backup复选框，这个特性就在服务器实例级别被激活了。　　

　　图7

　　注意： 数据库备份压缩特性只在企业版中有效，而恢复压缩数据库备份在SQL Server 2008的所有版本上都有效。

现在，让我们对我们的AdventureWorks数据库执行数据库备份压缩。　

　　　BACKUPDATABASEAdventureWorks
　　TODISK='C:SQLDataAdventureWorks_compressed.bak'
　　WITHCOMPRESSION,INIT,STATS=10

　　注意，在BACKUP DATABASE命令中没有什么新的，除了在WITH条件从句里包含了COMPRESSION关键字。这告诉SQL Server压缩这个数据库备份，因为默认情况下压缩是关闭的。压缩的备份数据库过程花费了大约20.47 seconds，备份大小为35.31 MB——在大小和时间上都有显著的降低。

　　图3

　　图4

　　你可以使用下面的脚本来比较执行一个压缩备份和一个未压缩备份在时间上的不同。　

　　DECLARE@timeDiffDATETIME
　　SET@timeDiff=GETDATE()
　　BACKUPDATABASEAdventureWorks
　　TODISK='C:SQLDataAdventureWorks_uncompressed.bak'
　　WITHINIT
　　SELECTDATEDIFF(ms,@timeDiff,GETDATE())ASuncompressed_time_in_ms　
　　SET@timeDiff=GETDATE()
　　BACKUPDATABASEAdventureWorks
　　TODISK='C:SQLDataAdventureWorks_compressed.bak'
　　WITHCOMPRESSION,INIT
　　SELECTDATEDIFF(ms,@timeDiff,GETDATE())AScompressed_time_in_ms

　　你可以检查msdb数据库中backupmediaset和backupset表中表示备份压缩的新字段。在backupset表中，你可以看到compressed_backup_size字段，它表示了磁盘上存储的备份总字节数。你可以使用backup_size和compressed_backup_size来计算压缩比。

问题

　　大型数据库的数据库备份和维护窗口总是受阻，特别是当我们需要完全数据库备份来初始化一个数据库镜像或事务日志传送会话的时候。当我们浏览SQL Server 2008的新特性时，我们发现由一个叫做数据库备份压缩的特性，可以用它来显著地降低备份和恢复操作。你能给我们详细地介绍下如何使用它吗?

　　专家解答

　　数据库压缩是SQL Server 2008的一个新特性，它可以显著地降低备份和恢复操作。默认情况下，备份压缩是在服务器实例级别上关闭的。不像其它提供不同级别压缩的第三方软件，SQL Server 2008只提供了一个级别。

　　在这篇文章里，我们将看看怎样执行数据库备份压缩和比较一下一个普通数据库备份的备份和恢复时间与压缩数据库备份的备份和恢复时间，以及备份文件的大小。首先让我们给AdventureWorks数据库创建一个完全备份，这个数据库的大小大约为178.75 MB。我选择使用一个TSQL脚本来进行备份和恢复，所以我可以看到这个过程它花费了多少时间。我是在一个具有2GB RAM的VMWare上运行的，所以可能和运行在物理服务器上不同。

　　　BACKUPDATABASEAdventureWorks
　　TODISK='C:SQLDataAdventureWorks_uncompressed.bak'
　　WITHINIT,STATS=10

　　默认情况下，在我例子中未压缩的备份数据过程花费了29.20秒，备份大小为132 MB。　

　　图1

小的时候，经常欠买零食吃，我觉得如果有天能有100块钱去买吃的，大概我就是世界上最幸福的人了。
高中的时候理想是考上大学，那样大概就能解放，从此革命大翻身。
后来大学了，恋爱了，工作了，经过了许多多事情，也算努力过，奋斗过，失望迷茫都有过，才明白，

其实人人都是处于生活之外的，换句话说，你所设想的美好是根本都不会实现的，最保所的做法就是不要对生活报什么期望。

好像说的有些绝望，但是看看周围发生的事情吧。

股市会随你所愿的再涨到6千点么？
房价会跌倒你能买得起么？
还有你不辞辛苦加班、耗尽青春的公司，年终奖的数字会让你眼前一亮么？
你觉得了？
好像太绝望了，以上的要求好难实现，
不如转移点注意力吧。

颛桥哪里新开了餐厅，和好友们去挫一顿
朋友家有什么喜事，大家一起去热闹一下
或者，多花点银子去看看阿凡达吧，至少这些我们这些一般的人能消费的起的。

我考上了大学，月收入也超过100块钱，我实现了小时候的理想，生后也不过是这样。
我最后一个理想希望来上海工资能翻倍，是实现了，可是房价也翻倍了~

生活的复杂远远超过了我的理解能力，至少在我们所生活的社会是这样。
当下年轻人还是糊涂一点，不如多关心理想之外的事情，那样会过得快乐些。

 

转移颛桥网论坛http://zhuanqiao.net/viewthread.php?tid=1968

做为一名开发人员，深切感受到技术更新之快。时常业务生疏被淘汰。
而常常追赶新技术，新模式，新框架成了常态。
而我所经历的一些开发主管，他们的共同之归纳来确都是一些简单的开发习惯，也比未见他们
多博大精深，或许这才是本质。

1 减少程序的依赖。
一个网站需要从不同的的取数据，而有的稳定有的不稳定，经常会发现不稳定的数据没取到，网站

就挂了，于是主管将不稳定的取到缓存里，虽然数据不会是最新，但是保证系统的高可用性。

2及时沟通。
这个表现在第二个公司里就是要好好检查自己的邮件，建立专门的规则，从而保证不遗漏重要的事

情。
然后是如果对某个需求有疑问或者是不确定的事情，一定要提出来，而不是要不啃声。见过很多工

作3年以内的新人都有这个问题，埋头做，到后来发现白做了。

3保证程序可配置性。
我写过一个发邮件的程序，一个程序在不同的时间发不同的邮件。当时我偷懒，居然把程序设计成

根据启动的时间不同，来发不同的邮件。结果搞得非常不好调试。此程序被主管死骂，后来改成传

参数来发邮件。
再一个就是对一些会变化的数据，比如数据库链接等，能遇见到可能会变的，一定不要写死，不然

别人调试代码的时候会骂你的。

4仔细的提交代码
发现N多人会遗漏提交代码，VSS和VS集成可能还好点，SVN就太多了，经常自己本地可以，别人编译

不过，一看发现少了文件。

5管理数据库
一般来说code会用source control管理，但数据库就少了，支持的软件不同。因为公司的关系，用

了vs自带的data base project。每次改动数据库结构都在data base里面改，然后发布到数据库。
这样好处是vs可以编译你的数据库，如果发现这个改动导致其他地方产生问题，比如你删除了sp，

但是另外一个sp再引用，就会报错。这样大大提高了协同工作的效率和出错概率。
同时data base project 通过一些插件可以事先一些静态数据的保存，这样很方便将一个数据库，

初始化发布到其他地方。

以上几点做过几年开发的都知道，但是能保证养成良好习惯的太少了。
做好了，随便去个IT公司当个经理什么的肯定没问题的。

This document describes a potential solution to managing static data in Visual Studio Team Edition for Database Professionals.

What is static data?
The problem
A possible solution
Data script generator tool

What is static data?

For the purposes of this document database data will be split into three categories:

1. User Data: Data entered by users of the system relating to their role in the application; for example, a user's contact details
2. Control Data: Data required by the system and is unlinked to user data, but is volatile in the sense that it can be modified by users of the system through the calling application; for example a welcome message on the front page of a website that a manager may wish to change based on the day of the year
3. Static Data: Data that is required in order for the system to function, but is not changed through the calling application; for example a list of employee statuses (Permanent, Contractor, Employment Ended). Usually used as 'database enums' with other tables having foreign keys pointing to them

The problem

Visual Studio Team Edition for Database Professionals is a superb program that manages the schema of your database visually and is capable of storing all content and changes in source control. Unfortunately it does not have a system to deal with static data, which makes it difficult to run unit tests and automated test plans based on a blank database created from the schema. Currently there are two ways round this; one is to use an insert script to populate the required tables with the correct data and reference the script in the post deployment script, and the other is to use a data generation plan with a weird setup to attempt to create the data with the correct IDs. Neither situation is fully workable though, as the former will cause problems when you attempt to run updates to a live database (as the data will already exist), and the latter will produce inconsistent data. In addition, neither solution allows us to manage our static data through source control. We could also store our static data in a separate database, however, we would lose change control over the data as it is stored out of the database project and in binary form.

A solution requires the following characteristics:

• We need to manage maintenance of the data, not just getting it into the database in the first place, e.g. I want to change the name of one of the entries in a control data table and have that change automatically processed as part of my next release to the live database along with any schema changes
• The data has to be held in text format to allow source control. We can't use a database to hold our data as this would not be practical for source control purposes
• The data needs to be synchronisable, but only one-way, for example we can run our changes against any database that is true to our schema and the data will not be duplicated in the table, but any rogue entries entered directly into the database will be passed back to the database project
• All this needs to be done as part of the normal release process inside DBPro

A possible solution

A nice solution would therefore be to have a script for each static data table which defines a table variable with the same signature as the target database, populates the variable using standard insert statements, and then the data in the variable can be used to update the target static data table. An example is shown below:

/***************************************
*** Static data management script ***
***************************************/

-- This script will manage the static data from
-- your Team Database project for tblEmployeeType.

PRINT 'Updating static data table tblEmployeeType'

-- Set to your region's date format to ensure dates are updated correctly
SET DATEFORMAT dmy

-- 1: Define table variable
DECLARE @tblTempTable TABLE (
EmployeeTypeId int,
Description varchar(50)
)

-- 2: Populate the table variable with data
-- This is where you manage your data in source control. You
-- can add and modify entries, but because of potential foreign
-- key contraint violations this script will not delete any
-- removed entries. If you remove an entry then it will no longer
-- be added to new databases based on your schema, but the entry
-- will not be deleted from databases in which the value already exists.
INSERT INTO @tblTempTable (EmployeeTypeId, Description) VALUES ('1', 'Permanent')
INSERT INTO @tblTempTable (EmployeeTypeId, Description) VALUES ('2', 'Contractor')
INSERT INTO @tblTempTable (EmployeeTypeId, Description) VALUES ('3', 'Employment Ended')


-- 3: Insert any new items into the table from the table variable
SET IDENTITY_INSERT tblEmployeeType ON
INSERT INTO tblEmployeeType (EmployeeTypeId, Description)
SELECT EmployeeTypeId, Description
FROM @tblTempTable WHERE EmployeeTypeId NOT IN (SELECT EmployeeTypeId FROM tblEmployeeType)
SET IDENTITY_INSERT tblEmployeeType OFF

-- 4: Update any modified values with the values from the table variable
UPDATE LiveTable SET
LiveTable.Description = tmp.Description
FROM tblEmployeeType LiveTable
INNER JOIN @tblTempTable tmp ON LiveTable.EmployeeTypeId = tmp.EmployeeTypeId

PRINT 'Finished updating static data table tblEmployeeType'

The above script will insert and update data in the target table based on the data entered in the second section. This allows the DBA to add and modify values, and the changes will automatically be propagated to the database on execution. The script can also be held inside the database project, and therefore also in source control, if required. The scripts should be held in a separate folder under the 'Scripts' folder in the database project called 'StaticData', and then the post-deployment script can call the individual update scripts like so:

:r .\StaticData\tblEmployeeType.staticdata.sql

Data Script Generator Tool

Update: Thank you to Carlos Vacca for pointing out an error when scripting a table with a composite primary key. This has been fixed in version 1.3.
Version 1.4 fixes a problem with null datetime columns being added as an empty string, which caused them to be added as date minvalue.

Note: If you are using the new GDR edition of DBPro then you will need to edit the DefaultTemplate.sql file in the installation directory (C:\Program Files\Static Data Script Generator) and add a GO statement to the end.

Of course, creating scripts for all static data tables in an existing database is a pain, as the table definition, the insert statements and the update statement will all need to be changed to match the schema of the table. Therefore you can download and use my Static Data Script Generator tool for free and use it to create your scripts for you.

To use the tool, install it using the link above, and navigate to the installation folder (default C:\Program Files\Static Data Script Generator). Change the connection string in the application configuration file (Static Data Script Generator.exe.config) to point to your database, and then run the application. The application will load all tables from your database into a listbox:

You can then select which tables you would like to create a script for. It is recommended to populate the tables with your initial data before creating the script (even just one record) in order to allow the script to create insert statements.

The application will create a script for each table, and therefore you need to provide an existing directory the application will drop the files into. Note that if the file already exists it will be overwritten. If you would like the application to create the post-deploy include statements for you to copy and paste into your post-deployment script then click the 'Create index script' checkbox. Click 'Generate Scripts' to create the files.

The application loads in an SQL template to drop the generated code into. You can change the format of this file by editing the DefaultTemplate.sql file in the application directory.

After the files have been generated, create a folder in your database project under the 'Scripts' directory called 'StaticData' and import the scripts into this folder. You can then copy and paste the contents of the index.txt file (if you checked the 'Create index script' option) into your Script.PostDeployment.sql.

Any problems / questions / suggestions? Email Me

RedGate

As an aside, I was contacted by the nice people at RedGate who pointed out that one of their products, SQL Data Compare, can also manage your static data for you. Usually data comparison tools only sync changes between live database tables, but SQL Data Compare allows you to generate DML files which can be executed after a build to keep your data in check. They are text files and so changes can also be managed in a source control system. Therefore if you already have RedGate's toolbelt then I would urge you to give this a try.

Of course, this is still quite a way away from the ideal solution, which is to have your data linked to your schema in source control, and preferably a nice designer similar to the SSMS 'Open Table' / 'Edit Top 200 Rows' functionality. RedGate also informed me of a product they are working on called SQL Source Control which will have the required functionality in one form or another.
*Waits patiently for the beta*